« Oracle 11gr2 : Aplicando nuestro primer PSU ABRIL 2010SQL : Como contar los registros de las tablas de usuarios en una sola consulta »

Oracle 11gr1 : Índices INVISIBLES (INVISIBLE INDEX)



A partir de Oracle11g nace una nueva característica que se llama índices INVISIBLES



¿Cuál es la gracia principal? Es que puedo deshabilitar los índices, sin necesidad de dejaros UNUSABLES y sin la necesidad de borrarlos, lo cual
implica que se pueden habilitar y deshabilitar de forma muy rápida.

En el fondo dejar un índice como INVISIBLE , es dejarlo fuera del alcance del optimizador cuando esta generando los planes de ejecución

Sólo imaginenlo para un índice de 10GB ¿lo borrarían para saber com oanda su aplicación? , creo que no... pues bien un índice INVISIBLE es la solución a la problemática.

Una de las principales ventajas de los índices invisibles sobre los índices no USABLES , es que los índices INVISIBLES si están afectados por las DML que se le pueda hacer a su tabla de orígen , o sea, mientras estén INVISIBLES, siguen actualizandose ... genial!!

Un ejemplo práctico

1.- Se genera una estructura de tabla con su clave primaria

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

by Ligarius
13.04.10. 15:02:29. 713 words, 9555 views. Categories: Base de datos, Oracle 11g, Tuning / Performance ,