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 possuí 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.

Artigo Escrito por: Anderson Graf e Jhonata Lamim

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)

Autor: Anderson Graf

Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Entusiasta da tecnologia Oracle, ACE Associate ♠, autor em vários blogs e OTN. Consultor Oracle Senior na Exímio Soluções em TI

Envolvido em soluções de:
– Implementação, migração, gerenciamento e suporte a produtos Oracle, multiplataforma
– Monitoramento de ambientes 24×7
– Backup e Recovery
– Performance e Tuning
– Alta disponibilidade (HA) – RAC, Data Guard
– EM database/grid/cloud control
– Particionamento & Advanced Compression
– Oracle Engineered Systems – ODA, Exadata

Blog pessoal: http://www.andersondba.com.br
Articulista na Oracle Technology Network (OTN) – https://goo.gl/99R6yW
ACE Associate – https://goo.gl/MBB51b
Articulista GPO – http://profissionaloracle.com.br

• 
• 
• 
• 
•