O processo de “Tuning” de SQL normalmente requer o teste de
estratégias alternativas de indexação para ver o efeito sobre os planos de
execução. Adicionar índices extras para tabelas grandes pode levar uma
quantidade considerável de tempo e espaço em disco. Os índices adicionais estão
disponíveis para uso por outras sessões, o que pode afetar o desempenho de
outras partes do aplicativo que você não está atualmente testando. Isso pode
ser problemático quando você está tentando identificar problemas em um sistema
de produção.
Comparado com os índices convencionais, um índice virtual não tem
nenhum segmento associado, para que o tempo de criação e de espaço em disco
associado são irrelevantes. Além disso, não é visto por outras sessões, para
que ele não afete o funcionamento normal do sistema. Este artigo, apresen-tará um
exemplo da utilização dos índices virtuais.
Primeiro, vamos criar e
preencher uma tabela.
SQL> CREATE TABLE
objects_tab
AS
SELECT * FROM all_objects;
SQL> ALTER TABLE
objects_tab ADD (
CONSTRAINT
objects_tab_pk PRIMARY KEY (object_id));
SQL> EXEC
DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);
Vamos gerar o plano de
execução.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
92 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB |
1 | 92 | 2
(0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 |
| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Se a consulta for
executada por uma coluna não indexada, o plano começa a realizar o “TABLE FULL
SCAN”.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 2 | 184 |
207 (5)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 |
184 | 207 (5)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='USER_TABLES')
Para criar um índice
virtual, basta adicionar a cláusula NOSEGMENT para a instrução CREATE INDEX.
SQL> CREATE INDEX
objects_tab_object_name_vi
ON objects_tab(object_name) NOSEGMENT;
Index Created.
Ao repetir a consulta
anterior, podemos ver o índice virtual não é visível ao otimizador.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 2 | 184 |
207 (5)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 |
184 | 207 (5)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("OBJECT_NAME"='USER_TABLES')
Para disponibilizar o índice
virtual deve-se definir o parâmetro “ _USE_NOSEGMENT_INDEXES”.
SQL>
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session Altered.
Ao repetirmos a
consulta, vemos que o índice virtual é usado agora.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 |
184 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 2 |
184 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 2 |
| 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("OBJECT_NAME"='USER_TABLES')
Os índices virtuais não
aparecem na visão DBA_INDEXES, mas aprecem na visão DBA_OBJECTS.
SQL> SET AUTOTRACE
OFF
SQL> SELECT
index_name FROM user_indexes;
INDEX_NAME
------------------------------
OBJECTS_TAB_PK
1 row selected.
SQL> SELECT
object_name FROM user_objects WHERE object_type = 'INDEX';
OBJECT_NAME
---------------------------
OBJECTS_TAB_PK
OBJECTS_TAB_OBJECT_NAME_VI
2 rows selected.
Estatísticas podem ser geradas
em índices virtuais da mesma forma como índices regulares, porém não serão
geradas informações na view DBA_INDEXES.
SQL> EXEC DBMS_STATS.gather_index_stats(USER,
'objects_tab_object_name_vi');
PL/SQL procedure successfully completed.
O banco de dados Oracle não
permite criar outro índice virtual com a mesma relação de colunas, mas permite
criar um índice real com a mesma relação de colunas.
SQL> CREATE INDEX
objects_tab_object_name_vi2
ON objects_tab(object_name) NOSEGMENT;
CREATE INDEX
objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column
list already indexed
SQL> CREATE INDEX
objects_tab_object_name_i ON objects_tab(object_name);
Index created.
Rubens Oliveira
DBA Oracle Consultor
