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