Blog
Oracle Virtual Index ou Fake Index
O gerenciamento e ajuste de desempenho de consultas no banco de dados (tuning) é uma tarefa que requer conhecimento técnico e investimento de tempo considerável.
A criação de índices faz parte do dia a dia do tuning de consultas e algumas vezes pode ser uma tarefa um tanto quanto demorada, principalmente se o mesmo estiver sendo criado em tabelas muito grandes.
Levando em consideração que toda alteração deve ser testada antes de ser aplicada em produção, a criação de um índice não foge à regra.
Afim de reduzir o tempo gasto no processo de validação do índice criado, podemos utilizar a opção de criar um virtual index ou fake index. Desta forma podemos validar se o índice criado será utilizado pelo otimizador, sem termos realmente criado o mesmo. Ou seja, estamos reduzindo o tempo gasto na criação do índice para validação da utilização do mesmo pelo otimizador.
Um virtual index ou fake index é um índice cuja definição existe no dicionário de dados, porém o mesmo não possui segmentos criados. O principal propósito de um virtual index é simular a existência do mesmo sem a necessidade de realmente cria-lo.
Esta ação permite a execução de um explain para validação da utilização do índice, bem como verificarmos se o mesmo não terá um impacto negativo no plano de execução sem que ele realmente exista.
Para ilustrar seu funcionamento foi criado o seguinte cenário:
- Tabela de teste
SQL> create table exemplo as select * from dba_tables; Table created.
- Explain de uma query aleatória sobre a tabela
SQL> set autotrace traceonly explain SQL> select * from exemplo where table_name='AUD$'; Execution Plan ---------------------------------------------------------- Plan hash value: 760791384 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1430 | 23 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EXEMPLO | 1 | 1430 | 23 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TABLE_NAME"='AUD$') Note ----- - dynamic statistics used: dynamic sampling (level=2)
- Criando um índice virtual (fake) sobre a coluna de pesquisa
SQL> create index exemplo_idx1 on exemplo (table_name) nosegment; Index created.
- Confirmando que não existem segmentos criados para o índice da mesma forma que ele não consta na dba_indexes, mas é listado como qualquer outro objeto na dba_objects.
SQL> set autotrace off SQL> select * from dba_segments where segment_name='EXEMPLO_IDX1'; no rows selected SQL> select * from dba_indexes where index_name='EXEMPLO_IDX1'; no rows selected SQL> col object_name for a20 SQL> col object_type for a20 SQL> select object_name, object_type from dba_objects where object_name='EXEMPLO_IDX1'; OBJECT_NAME OBJECT_TYPE -------------------- -------------------- EXEMPLO_IDX1 INDEX
- Reexecutando a query para verificar a utilização do índice
SQL> set autotrace traceonly explain SQL> select * from exemplo where table_name='AUD$'; Execution Plan ---------------------------------------------------------- Plan hash value: 760791384 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1430 | 23 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EXEMPLO | 1 | 1430 | 23 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TABLE_NAME"='AUD$') Note ----- - dynamic statistics used: dynamic sampling (level=2)
Observe que o index ainda não foi utilizado, isto ocorre pois é preciso ajustar o parâmetro _USE_NOSEGMENT_INDEXES para true na sessão.
- Ajustando o parâmetro _USE_NOSEGMENT_INDEXES para TRUE
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true; Session altered.
- Reexecutando a query
SQL> select * from exemplo where table_name='AUD$'; Execution Plan ---------------------------------------------------------- Plan hash value: 1811059678 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1430 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EXEMPLO | 1 | 1430 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EXEMPLO_IDX1 | 7 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TABLE_NAME"='AUD$') Note ----- - dynamic statistics used: dynamic sampling (level=2)
Uma vez setado o parâmetro oculto o otimizador passa a utilizar o virtual index criado para a tabela.
Se a query for executada a partir de outra sessão, execute o "alter session" para o otimizador utilizar o virtual índice. Algumas considerações sobre os índices virtuais ou fakes indexes:
- É possível realizar analyze do virtual index:
SQL> analyze index EXEMPLO_IDX1 validate structure; Index analyzed. SQL> analyze index EXEMPLO_IDX1 compute statistics; Index analyzed.
- Não é possível realizar rebuild de um virtual index. O erro ORA-8114 será gerado:
SQL> alter index EXEMPLO_IDX1 rebuild; alter index EXEMPLO_IDX1 rebuild * ERROR at line 1: ORA-08114: can not alter a fake index
- É possível remover o virtual index normalmente como qualquer outro índice:
SQL> drop index EXEMPLO_IDX1; Index dropped.
- Nas versões 11.2.0.3 e 11.2.0.4 existe a confirmação do BUG 18490543 que pode ocorrer e impedir o MOVE da tabela (ALTER TABLE .. MOVE). Quando o BUG ocorrer o processo de move será cancelado com o erro ORA-600 [25027][0][0]. Uma workaround é remover os índices virtuais antes do MOVE e recria-los (se necessário) após ou aplicar o patch/migrar para as versões onde o BUG foi corrigido (12.1.0.2 – Server Patch Set e 12.2.0.1 – Base Release).
SQL> alter table EXEMPLO_2 move compress for oltp initrans 4 parallel 6; alter table EXEMPLO_2 move compress for oltp initrans 4 parallel 6 * ERROR at line 1: ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [], [], [], [], [], [] SQL> SELECT index_owner, index_name FROM dba_ind_columns WHERE table_name='EXEMPLO_2' MINUS SELECT owner, index_name FROM dba_indexes where table_name='EXEMPLO_2'; INDEX_OWNER INDEX_NAME ------------------------------ ------------------------------ SYS EXEMPLO_2_IDX1 SYS EXEMPLO_2_IDX2 SQL> select dbms_metadata.get_ddl('INDEX','EXEMPLO_2_IDX1') from dual; DBMS_METADATA.GET_DDL('INDEX','EXEMPLO_2_IDX1') ------------------------------------------------------------------------------ CREATE INDEX "SYS"."EXEMPLO_2_IDX1" ON "SYS"."EXEMPLO_2" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOSEGMENT SQL> select dbms_metadata.get_ddl('INDEX','EXEMPLO_2_IDX2') from dual; DBMS_METADATA.GET_DDL('INDEX','EXEMPLO_2_IDX2') ------------------------------------------------------------------------------ CREATE INDEX "SYS"."EXEMPLO_2_IDX2" ON "SYS"."EXEMPLO_2" ("NM_COMP") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOSEGMENT SQL> drop index EXEMPLO_2_IDX1; Index dropped. SQL> drop index EXEMPLO_2_IDX2; Index dropped. SQL> alter table EXEMPLO_2 move; Table altered. SQL> CREATE INDEX "SYS"."EXEMPLO_2_IDX1" ON "SYS"."EXEMPLO_2" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOSEGMENT; Index created. SQL> CREATE INDEX "SYS"."EXEMPLO_2_IDX2" ON "SYS"."EXEMPLO_2" ("NM_COMP") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOSEGMENT; Index created.
204 visualizações
