A partir da versão do Oracle 11g, é possível criar e alterar os índices para INVISIBLE/VISIBLE.
Ao alterar um índice para invisible, por default o otimizador do Oracle não utiliza o mesmo para montar um plano de execução. A menos, que o parâmetro optimizer_use_invisible_indexes (default FALSE) esteja definido como TRUE a nível de instância ou de sessão. Neste último, o índice também será usado apenas a nível de sessão.
Com esta feature, é possível realizar a criação de um índice invisible em produção sem afetar os planos de execução existentes. Essa possibilidade é bastante útil quando, por exemplo, não temos um ambiente de testes e precisamos validar o impacto da criação do índice direto no ambiente de produção.
Abaixo vou exemplificar o uso desta feature:
Primeiramente iremos criar e popular uma tabela e realizar a criação do índice invisible, para que possamos realizar os testes com o uso da feature invisible index.
Primeiramente iremos criar e popular uma tabela e realizar a criação do índice invisible, para que possamos realizar os testes com o uso da feature invisible index.
SQL> conn lamim/lamim
SQL> create table tst_invisible_ix (
Connected.
SQL>
numero number
);
SQL> BEGIN
2 3
Table created.
INSERT INTO tst_invisible_ix VALUES (i);
FOR i IN 1 .. 10000 LOOP
END LOOP;
COMMIT;
PL/SQL procedure successfully completed.
END;
/ 2 3 4 5 6 7
SQL> SQL>
SQL>
Antes de efetivamente realizar os testes, irei realizar uma coleta de estatística da tabela recém criada.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tst_invisible_ix', cascade=> TRUE); PL/SQL procedure successfully completed.Agora vamos validar o comportamento do plano de execução com e sem um índice em modo invisible.
SQL> show parameter invisible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SQL> select * from tst_invisible_ix where numero=123;
NUMERO
----------
123
Execution Plan
----------------------------------------------------------
Plan hash value: 978504830
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TST_INVISIBLE_IX | 1 | 4 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUMERO"=123)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
No exemplo acima é possível perceber que a consulta realiza um acesso full a tabela e não utiliza o índice criado com o invisible. Para testarmos a utilização do índice, podemos usar um hint na query ou alterar o parâmetro optimizer_use_invisible_indexes a nível de sessão, evitando assim que o índice impacte em outros processos e/ou aplicações.
SQL> select * from tst_invisible_ix where numero=123;
NUMERO
----------
123
Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_TST_INVISIBLE_IX01 | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NUMERO"=123)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Podemos notar que após a alteração do parâmetro a consulta não realiza mais um acesso full e passa a utilizar o índice. Lembrando que o uso do índice ocorre apenas na sessão atual, devido a alteração do parâmetro optimizer_use_invisible_indexes.
Para tornar o índice visível para todo o banco, basta executar o comando alter index index_name visible, da mesma forma se desejarmos alterar um índice já criado para invisivel, basta executar um alter index_name inivisible.
SQL> conn lamim/lamim
Connected.
SQL> alter index LAMIM.IX_TST_INVISIBLE_IX01 visible;
Index altered.
SQL> set autotrace on
SQL> select * from lamim.tst_invisible_ix where numero=123;
NUMERO
----------
123
Execution Plan
----------------------------------------------------------
Plan hash value: 1674343203
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_TST_INVISIBLE_IX01 | 1 | 4 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NUMERO"=123)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
Afim de validar se um índice está visivel ou invisível, podemos consultar a coluna visibility da dba/user_indexes.SQL> select index_name, visibility from dba_indexes where index_name='IX_TST_INVISIBLE_IX01'; INDEX_NAME VISIBILIT ------------------------------ --------- IX_TST_INVISIBLE_IX01 VISIBLE
Fonte:https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm
http://www.orafaq.com/wiki/Invisible_indexes
Comentários
Postar um comentário