New Feature do 11g (Invisible index)

Uma nova funcionalidade do Oracle 11g é o índice invisível, que serve para testar como uma query irá se comportar sem o índice. Isso é bem útil quando se tem um índice bem grande e o trabalho para reconstruí-lo consumiria muita CPU e I/O.

Abaixo segue um teste e os planos de execução:

——– Versão do oracle testada ———————

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

SQL> create table teste1 tablespace users as select * from all_objects;

Tabela criada.

SQL> create index in_teste1 on teste1 (owner) tablespace users;

Índice criado.

SQL> set autotrace traceonly explain

SQL> select * from teste1 where owner=’SYSTEM’;

Plano de Execução
———————————————————-
Plan hash value: 2371655831

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 489 | 77262 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTE1 | 489 | 77262 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_TESTE1 | 489 | | 2 (0)| 00:00:01 |
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“OWNER”=’SYSTEM’)

Note
—–
– dynamic sampling used for this statement

SQL>
SQL> ALTER INDEX IN_TESTE1 INVISIBLE;

Índice alterado.

SQL>
SQL> SELECT * FROM TESTE1 WHERE OWNER=’SYSTEM’;

Plano de Execução
———————————————————-
Plan hash value: 110554063

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 621 | 98118 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TESTE1 | 621 | 98118 | 282 (1)| 00:00:04 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OWNER”=’SYSTEM’)

SQL> ALTER INDEX IN_TESTE1 VISIBLE;

Índice alterado.

SQL> SELECT * FROM TESTE1 WHERE OWNER=’SYSTEM’;

Plano de Execução
———————————————————-
Plan hash value: 2371655831

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 489 | 77262 | 15 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTE1 | 489 | 77262 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_TESTE1 | 489 | | 2 (0)| 00:00:01 |
—————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“OWNER”=’SYSTEM’)

Como podemos perceber, o metodo de acesso que o oracle escolheu variou com índice visível e ínsivel de range scan para um full scan. Mesmo invisível o oracle ainda dá manutenção (DML) no índice.