Pular para o conteúdo principal

Invisible Indexes

11G has a new feature called Invisible Indexes. An invisible index is invisible to the optimizer as default.
Using this feature we can test a new index without effecting the execution plans of the existing sql statements or we can test the effect of dropping an index without dropping it.

SQL> create table teste_ii (a number, b varchar2(10));

SQL> create index idx_invisible on teste_ii(id);

begin
for i in 1..20
loop
insert into teste_ii(a,b) values(i,'TESTE'||i);
end loop;
end;
/
commit;

SQL> select * from teste_ii where a=10;
A B
---------- ----------
10 TESTE10

SQL> explain plan for
2 select * from teste_ii where a=10;
Explained

SQL> sELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 726495292
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| TESTE_II | 1 | 10 | 2 (0
|* 2 | INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=10)

Set index to invisible:
SQL> alter index denilson.idx_invisible invisible;
Index altered

SQL> explain plan for
2 select * from teste_ii where a=10;
Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2149228497
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TESTE_II | 1 | 10 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=10)


Force the optmizer to get the index in the your session:
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;
Session altered

The index still invisible.

SQL> select index_name,table_name,status,VISIBILITY from user_indexes where index_name = 'IDX_INVISIBLE';
INDEX_NAME TABLE_NAME STATUS VISIBILITY
--------------------------- ----------------------- ----------- ----------
IDX_INVISIBLE TESTE_II VALID INVISIBLE

SQL> explain plan for
2 select * from teste_ii where a=10;
Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 726495292
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0
| 1 | TABLE ACCESS BY INDEX ROWID| TESTE_II | 1 | 10 | 2 (0
|* 2 | INDEX RANGE SCAN | IDX_INVISIBLE | 1 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=10)

Comentários

Postagens mais visitadas deste blog

Index SKIP SCAN???

Index Skip Scan nada mais é que o "split" logico de um indice composto, ou seja, se sua clausula WHERE não tiver todos os campos do indice composto, ele é dividido em menores sub-indices Exemplo: -------- SQL> create table teste(a number,b number); Table created. SQL> begin 2 for i in 1..1000 3 loop 4 insert into teste values(i, 56); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create index idx_teste on teste(a,b); Index created. SQL> analyze table teste compute statistics; Table analyzed. SQL> explain plan for select /*+ index_ss(teste idx_teste) */ * from teste where b=56; Explained. SQL> start ?\rdbms\admin\utlxpls.sql -------------------------------------------------------------------------------- | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | -------------------------------------------------------------------------------- | SELECT STATEMENT | | 1

Webcenter Content - Jython to Checkin Content with Attachments (CHECKIN_NEW_WITH_RENDITIONS)

In the last few days, i had to load a content that, by its requirements, needs to attach files to the content. My first alternative would be Batch Loading, however for the bacth loder file it was not possible because it does not actually support the rendition service. With the next option, i tried to use the IdcCommand, but still had many problems, such as errors below: sample hda file: [oracle @ hostname bin] $ cat idctst.txt @Properties LocalData IdcService = EDIT_RENDITIONS dID = 37020 renditionKeys = addRendition0 addRendition0.name = test543 addRendition0.action = edit addRendition0.file = test.txt addRendition0.file: path = / u01 / oracle / domains / content_domain / ucm / cs / vault / ~ temp / test.txt @end << EOD >> Executing: [oracle @ hostname bin] $ ./IdcCommand -f idctst.txt -u sysadmin -l log.log -c server Error: Java HotSpot (TM) 64-Bit Server VM warning: Using the ParNew young collector with the Serial old collector is deprecated and w

Configurar Tamanho de UNDO e tempo de RETENÇÃO (UNDO_RETENTION)

-- Tamanho Undo Atual SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#; -- Blocos de UNDO por Seg. SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; --Tamanho do Bloco SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size'; -- Select calcula UNDO RETENTION SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO'