Índices invisiveis – 11g

Na versão 11g, o Oracle introduziu um conceito já à algum tempo pedido pelos DBAs (e não só) que se prende com a criação de indices visiveis ou invisiveis. A teoria é simples: Aquando da criação do indices podemos especificar se o indice é ou não invisivel para o CBO:

 


SQL> create table a_test as select table_name, tablespace_name, status from user_tables;

Table created.

SQL> create index idx_a_test on a_test(status);

Index created.

SQL> select index_name, index_type, visibility from user_indexes where lower(index_name) = 'idx_a_test';

INDEX_NAME INDEX_TYPE VISIBILIT
------------------------------ --------------------------- ---------
IDX_A_TEST NORMAL VISIBLE

Verificar agora que o CBO usa o indice com o estado visivel:

SQL> explain plan for select * from a_test where status='VALID';


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1704479005

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 680 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A_TEST | 17 | 680 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_A_TEST | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

2 - access("STATUS"='VALID')

Note
-----
- dynamic sampling used for this statement (level=2)

18 rows selected.


Como podemos perceber, é feito um range scan no índice IDX_A_TEST para devolver os resultados baseados no
critério que fizemos. Vamos agora colocar o índice invisivel e verificar de novo:

SQL> alter index idx_a_test INVISIBLE;

Index altered.

SQL> explain plan for select * from a_test where status='VALID';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 680 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A_TEST | 17 | 680 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

1 - filter("STATUS"='VALID')

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

Verificando o plano afectado à mesma query, agora com o índice invisivel, percebemos que o CBO não usa o índice e naturalmente irá optar por um FTS à tabela a_test.
No entanto é possível também alterar este comportamento recorrendo a um parâmetro a nível da sessão que nos permitirá para cada sessão, especificar se o CBO usa ou não os índices invisiveis:
 


SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;

Session altered.


SQL> select index_name, index_type, visibility from user_indexes where lower(index_name) = 'idx_a_test';

INDEX_NAME INDEX_TYPE VISIBILIT
------------------------------ --------------------------- ---------
IDX_A_TEST NORMAL INVISIBLE



SQL> explain plan for select * from a_test where status='VALID';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1704479005

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 680 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| A_TEST | 17 | 680 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_A_TEST | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
---------------------------------------------------

2 - access("STATUS"='VALID')

Note
-----
- dynamic sampling used for this statement (level=2)

18 rows selected.

Facilmente podemos reparar que mesmo que o estado do índice seja INVISIBLE se definirmos o parâmetro OPTIMIZER_USE_INVISIBLE_INDEXES o CBO, como devia, vai fazer uso do índices visiveis e invisiveis.

Como nota final desta simples explicação existia um bug# 6344547 no metalink que não permitia gerar estatísticas quando o estado
do índice é invisivel e quando o parâmetro optimizer_use_invisible_index=false. No entanto comprovei que a versão 11g Express Edition Release 11.2.0.2.0 – 64bit Beta já não contem o bug:
 


SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> exec dbms_stats.gather_index_stats(user, 'idx_a_test');

PL/SQL procedure successfully completed.

Espero ter sido claro.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s