Neste artigo abordarei
sobre índices invisíveis no Oracle 11g, importante dispositivo que permite
testar e validar a utilização e necessidade de um índice num ambiente produtivo
de banco de dados.
O Oracle 11g permite que
os índices sejam marcados como invisíveis. Os
índices invisíveis são mantidos como qualquer outro índice, mas eles são
ignorados pelo otimizador, a menos que o parâmetro
OPTIMIZER_USE_INVISIBLE_INDEXES esteja definido como TRUE. Os índices podem ser
criados como invisíveis, usando a palavra-chave INVISIBLE, e sua visibilidade
pode ser alterada usando o comando ALTER INDEX.
Exemplo de criação de um
índice invisível:
Ativação/desativação de um índice:
ALTER INDEX INVISIBLE;
ALTER INDEX VISIBLE;
O script a
abaixo cria e preenche uma tabela, em seguida, cria um índice invisível.
Criação da tabela “TEST_TAB”:
CREATE TABLE TEST_TAB(id NUMBER);
Preenchimento
de dados na tabela “TEST_TAB”.
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO TEST_TAB VALUES (i);
END LOOP;
COMMIT;
END;
/
Criação do índice invisível,
“TEST_TAB_ID” para a tabela “TEST_TAB”.
CREATE
INDEX TEST_TAB_ID ON TEST_TAB(id) INVISIBLE;
Atualização
das estatísticas da tabela “TEST_TAB” e do índice “TEST_TAB_ID”.
EXEC
DBMS_STATS.gather_table_stats(TEST, 'TEST_TAB', cascade=> TRUE);
Para demonstrar como um
índice invisível funciona, vamos executar uma consulta usando a coluna
indexada, definida na cláusula WHERE.
SET
AUTOTRACE ON
SELECT
* FROM test_tab WHERE id = 999;
------------------------------------------------------------------------------
|
Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------==--------------------------------------
| 0 | SELECT STATEMENT | | 1 |
3 | 7 (0)| 00:00:01 |
|*
1 | TABLE ACCESS FULL| TEST_TAB
| 1 | 3 | 7
(0)| 00:00:01 |
------------------------------------------------------------------------------
Pode-se verificar que o índice
foi ignorado, pois a operação de “TABLE ACCESS FULL” é apresentada no plano de
execução da consulta, essa operação realiza a varredura completa da tabela
desprezando o índice da tabela.
Ao definir o parâmetro
OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE e ativando a “visibilidade” do
índice, o otimizador utiliza o índice durante a execução da consulta, conforme
demonstrado abaixo.
ALTER
SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
--------------------------------------------------------------------------------
|
Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 3 | 1
(0)| 00:00:01 |
|*
1 | INDEX RANGE SCAN| TEST_TAB_ID
| 1 | 3 |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Porém, ao
tornar o índice visível, mas com o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES
definido como FALSE, essa instrução ainda indicará ao otimizador que o índice
ainda disponível para uso.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
ALTER INDEX test_tab_id VISIBLE;
SELECT
* FROM test_tab WHERE id = 9999;
--------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 3 |
1 (0)| 00:00:01 |
|* 1 |
INDEX RANGE SCAN| TEST_TAB_ID |
1 | 3 | 1
(0)| 00:00:01 |
--------------------------------------------------------------------------------
Para
resolver esse impasse deve-se modificar o índice para INVISIBLE.
Índices
invisíveis podem ser uteis para processos com necessidades específicas de
indexação, onde a presença dos índices pode afetar negativamente outras áreas
funcionais. Eles também são úteis para testar o impacto de descartar um índice.
A visibilidade dos índices pode ser implementada configurando o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES como TRUE, para todo o banco de dados através da configuração do arquivo “spfile” ou do “init.ora” ou ainda pode ser implementado através da sessão de um usuário. A ativação ou desativação desse parâmetro é dinâmica.
Para
verificar os estados de visibilidade de um índice deve-se verificar a coluna
“VISIBILITY” das visões [DBA | ALL | USER] _INDEXES.
Rubens Oliveira
DBA Oracle Consultor
olivert.dba@consultant.com
