Oracle Virtual Index ou Fake Index

Por Anderson Graf Oracle Associate e Jhonata Lamim Oracle Associate
Publicado em Abril 2018

Revisado por Marcus Vinicius Miguel Pedro



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:

1) Tabela de teste

SQL> create table  exemplo as select * from dba_tables;

Table created.



2) 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)



3) Criando um índice virtual (fake) sobre a coluna de pesquisa

SQL> create index exemplo_idx1 on exemplo (table_name) nosegment;

Index created.



4) 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



5) 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.



6) Ajustando o parâmetro _USE_NOSEGMENT_INDEXES para TRUE

SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;

Session altered.



7) 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.
      
      

Referências:

Virtual Indexes (Doc ID 1401046.1)
Bug 18490543 - ORA-600 [25027][0][0] from ALTER TABLE .. MOVE with nosegment index (Doc ID 18490543.8)




Anderson Graf, Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Trabalha com banco de dados Oracle desde 2009, é Oracle OCP 10g/11g/12c, OCS Linux, Database e Cloud Control. OCE Performance Tuning; OPNCS. É um entusiasta da tecnologia Oracle e autor dos blogs andersondba.com.br e oraclehome.com.br.

Jhonata Euclides Lamim, MBA em Gestão de Banco de Dados Oracle pelo Centro Universitário de Araraquara (UNIARA), Bacharel em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE). Atua como DBA Oracle desde 2010, pela Teiko Soluções em Tecnologia da Informação. É um entusiasta da tecnologia Oracle e autor do blog lamimdba.com.br.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.