Oracle: Criando índice virtual

Como de costume, cada vez mais acho recursos super interessantes no Oracle. Dessa vez, trata-se da criação de índice virtual, que é a criação de índice sem storage para a árvore do mesmo. O banco simplesmente simula o acesso como se houvesse o índice real. A utilidade de tal recurso sem dúvida é para possibilitar o teste do comportamento do CBO quanto ao uso do índice, principalmente em uma tabela onde há uma grande massa de dados, o que tomaria bastante tempo e disco se não fosse virtual.

Vamos a um teste no Oracle 10g! (Embora este recurso seja bem antigo, no 8i já existia!)

O detalhe na criação do índice que irá indicar que o mesmo é virtual, é a cláusula NOSEGMENT. Veja abaixo um exemplo, onde testo na criação de um índice baseado em função (FBI):

SQL> create index t_virtual_idx on t(lower(owner)||'-'||lower(object_name)) nosegment; 
 
Índice criado.

Agora vamos a execução da query:

SQL> set autotrace traceonly explain 
SQL> select object_name from t where lower(owner)||'-'||lower(object_name) = 'felipe-t';
 
Plano de Execução
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |    25 |   850 |   461   (8)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T	 |    25 |   850 |   461   (8)| 00:00:06 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(LOWER("OWNER")||'-'||LOWER("OBJECT_NAME")='felipe-t')

Perceba que o otimizador utilizou FTS (Full Table Scan) para resolver a query, ou seja, não utilizou o índice. Mas isso é o esperado! Para o índice ser usado nós precisamos habilitar o parâmetro _use_nosegment_indexes.

SQL> alter session set "_use_nosegment_indexes" = true;
 
Sessão alterada.
 
SQL> select object_name from t where lower(owner)||'-'||lower(object_name) = 'felipe-t';
 
Plano de Execução
----------------------------------------------------------
Plan hash value: 1621160168
 
---------------------------------------------------------------------------------------------
| Id  | Operation		            | Name	        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	        |		        |	 25 |	850 |	  5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T 	        |	 25 |	850 |	  5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	        | T_VIRTUAL_IDX |  1689 |	    |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(LOWER("OWNER")||'-'||LOWER("OBJECT_NAME")='felipe-t')

Embora não tenha storage, o índice aparece no dicionário:

SQL> select index_name from user_ind_expressions;
 
INDEX_NAME
--------------------------------------------------------------------------------
T_VIRTUAL_IDX

Quanto essa questão de visualizá-lo no dicionário, há um detalhe, se você criar um índice sem ser baseado em função, ele não irá aparecer na view USER_INDEXES (desde o 9i, ele aparecia na 8i). Veja abaixo:

SQL> create index t_virtual_idx2 on t(owner) nosegment; 
 
Índice criado.
 
SQL> select index_name from user_indexes;
 
não há linhas selecionadas

Embora curiosamente, ele irá aparecer na view USER_OBJECTS.

SQL> select object_name from user_objects where object_type = 'INDEX';
 
OBJECT_NAME
--------------------------------------------------------------------------------
T_VIRTUAL_IDX2

Ficando satisfeito com o efeito do índice, é dropar e criá-lo sem o NOSEGMENT. E definir o que tiver que definir (INITIAL, NEXT, etc) apropriadamente... (O que é assunto pra um longo post :D)

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options