Índices virtuais: Como melhorar a performance das consultas sem afetar outras no banco de dados

Índices Virtuais – Sem Segmentos

Acredito que tanto desenvolvedores como DBAs já precisaram criar índices para avaliar performance de uma consulta. De certa forma,  essa atividade pode demandar muito tempo, quando necessário indexar uma tabela grande, considerando ainda a  utilização de storage e processamento para indexação, ou mesmo por você não saber os efeitos de criar um índice e afetar outras consultas do seu banco.

Dado esse fato, podemos utilizar os índices virtuais, ou índices sem segmentos, onde abaixo exemplifico o seu funcionamento:

Criando uma tabela já populada:

SQL> create table tab_vindex as (select * from dba_objects where object_id is not null);

Table created.

Adicionando Primary Key para tabela:

SQL> ALTER TABLE tab_vindex add constraint pk_tab_vindex PRIMARY KEY (object_id);

Table altered.

Coletando estatísticas da Tabela e Índice:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tab_vindex', cascade=>TRUE);

PL/SQL procedure successfully completed.

Habilitando AUTOTRACE para Sessão:

SQL> SET AUTOTRACE TRACEONLY EXP

Verificando o Plano da Consulta utilizando a PK Criada:

SQL> SELECT * FROM tab_vindex where object_id = 100;

Execution Plan
---------------------------------------------------------- 
Plan hash value: 2213869000 
--------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 | 
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 1 | 100 | 2 (0)| 00:00:01 | 
|* 2 | INDEX UNIQUE SCAN | PK_TAB_VINDEX | 1 | | 1 (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - access("OBJECT_ID"=100) 

Verificando o plano da Consulta sem utilização de coluna indexada:

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
---------------------------------------------------------- 
Plan hash value: 4059569637 
-------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 | 
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 | 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
1 - filter("OBJECT_NAME"='TAB_VINDEX')

Criando Índice Virtual:

SQL> CREATE INDEX ix_tab_vindex ON tab_vindex(object_name) NOSEGMENT;

Index created.

Note que para a criação do índice eu utilizei a opção NOSEGMENT, contudo, não foi necessária a leitura dos dados da tabela, bem como a criação de um novo segmento estruturado e organizado (sort) para leitura e indexação.

Testando o plano com o Índice Virtual:

Nativamente os índices virtuais não são identificados pelo OTIMIZADOR, portanto, seu novo índice não afetará nenhuma consulta que envolva essa tabela para essa coluna para as demais sessões do bando de dados:

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
---------------------------------------------------------- 
Plan hash value: 4059569637 
-------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 | 
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 | 
-------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
1 - filter("OBJECT_NAME"='TAB_VINDEX')

PARA QUE O OTIMIZADOR PASSE A IDENTIFICAR SEU ÍNDICE SEM SEGMENTO BASTA CONFIGURAR O PARÃMETRO  “_use_nosegment_indexes”  PARA SUA SESSÃO:

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

Testando novamente a consulta com o parâmetro ativo para sua sessão:

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
---------------------------------------------------------- 
Plan hash value: 1624739785 
--------------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
--------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 2 | 200 | 2 (0)| 00:00:01 | 
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 2 | 200 | 2 (0)| 00:00:01 | 
|* 2 | INDEX RANGE SCAN | IX_TAB_VINDEX | 2 | | 1 (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - access("OBJECT_NAME"='TAB_VINDEX')

Desabilitando o AUTOTRACE

SQL> SET AUTOTRACE OFF

Como os demais tipos de índices, os índices virtuais ficam registrados nas views do dicionário de dados do seu banco e podem ser consultados normalmente:

SQL> SELECT index_name FROM user_indexes;

INDEX_NAME 
------------------------------ 
PK_TAB_VINDEX
IX_TAB_VINDEX 
SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME 
-------------------------------------------------------------------------------------------------------------------------------
PK_TAB_VINDEX 
IX_TAB_VINDEX 

Referências

• http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_5013.htm#SQLRF01209

Abraço