Oracle y yo

SQL> create table t
2 (column1 number not null,
3 constraint pk_t PRIMARY KEY (column1));

Table created.

2.- Le insertamos datos

SQL> insert into t (select rownum from dba_objects);

72137 rows created.

SQL> commit;

Commit complete.

3.- Llevamos a cabo una consulta para ver su plan de ejecución

SQL> explain plan for
2 select * from t
3 where column1 = 10000;

Explained.

SQL> set linesize 130
SQL> set pagesize 0
SQL>
SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1517170033

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - access("COLUMN1"=10000)

13 rows selected.

Como se puede apreciar, ocupa de forma clara , el índice PK_T

4.- Dejamos el índice como INVISIBLE y vemos nuevamente el plan de ejecución

SQL> alter index pk_t invisible;

Index altered.

SQL> explain plan for
2 select * from t
3 where column1 = 10000;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("COLUMN1"=10000)

13 rows selected.

Como se puede apreciar, ahora el índice no es VISIBLE , pero en el fondo no es visible para el optimizador, por el cual queda fuera de los planes de ejecución que el optimizador pueda generar.

5.- Aunque utilicemos explicitamente el nombre del índice con un hint, este no será utilizado

SQL> explain plan for
2 select /*+ index(t pk_t) */ * from t where column1 = 20000;

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 1183 | 32 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 91 | 1183 | 32 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("COLUMN1"=20000)

13 rows selected.

6.- Podemos ver el estado de un índice INVISIBLE consultando cualquier vista *_INDEXES

SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';

OWNER INDEX_NAME VISIBILIT
------------------------------ ------------------------------ ---------
SYS PK_T INVISIBLE

7.- Existe un parámetro de inicialización llamado OPTIMIZER_USE_INVISIBLE_INDEXES, que le indica al optimizador si toma o no en cuenta los índices en estado INVISIBLE cuando esta realizando los planes de ejecución, por defecto está en FALSE, o sea, no toma en cuenta los índices INVISIBLES.

SQL> show parameter optimizer_use_invi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

8.- Pero si cambiamos este parámetro y lo dejamos en TRUE, toma en cuenta los índices aunque ellos estén INVISIBLES

SQL> alter system set optimizer_use_invisible_indexes=TRUE scope=both;

System altered.

SQL> select owner , index_name , visibility from dba_indexes where index_name like 'PK_T';

OWNER INDEX_NAME VISIBILIT
------------------------------ ------------------------------ ---------
SYS PK_T INVISIBLE

SQL>
SQL> explain plan for
select /*+ index(t pk_t) */ * from t where column1 = 20000; 2

Explained.

SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1517170033

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_T | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

1 - access("COLUMN1"=20000)

13 rows selected.

Lee también sobre

Nueva característica de los índices UNUSABLES

Creando un índice INVISIBLE

Espero que les sirva