Indices Virtuales

Vamos a intentar explicar un poco que son y para sirven los índices virtuales en Oracle.

El propósito de este tipo de índices es el hecho de simular la existencia de uno real, pero sin la necesidad de su construcción, nos ayudaran a determinar utilizando CBO (evaluación del plan de ejecución basada en costos) si es bueno o no su creación “física”. Su definición se encuentra en el diccionario de datos (en ciertas vistas) pero si realizamos una consulta en la vista DBA_SEGMENTS no lo encontraremos.

Oracle posee una cláusula oculta mediante la cual nos permitirá crear este tipo de índices, la misma es “nosegment”.
Además de conocer esta clausula debemos tener en cuenta que para poder utilizarlos es necesario setear el parámetro oculto “_use_nosegment_indexes”

La creación de los mismos se realiza de la siguiente manera:

SQL> CREATE unique INDEX VIRTUAL_PRUEBA on table_name(col_name) NOSEGMENT;

Y para utilizarlo, como dijimos seteamos la siguiente variable:

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Veamos como buscamos el índice que creamos en las vistas del diccionario de datos:

SQL> select segment_name, segment_type, bytes
2 from dba_segments
3 where segment_name = 'VIRTUAL_PRUEBA';

no rows selected

SQL> select object_name, object_type, status
2 from dba_objects
3 where object_name = 'VIRTUAL_PRUEBA;

OBJECT_NAME |OBJECT_TYPE |STATUS
------------------|-----------------|---------------
VIRTUAL_PRUEBA |INDEX |VALID

SQL> select index_name, index_type, status
2 from dba_indexes
3 where index_name = 'VIRTUAL_PRUEBA';

INDEX_NAME |INDEX_TYPE |STATUS
------------------------------|------------|---------------
VIRTUAL_PRUEBA |NORMAL |VALID

Por último, quería mostrar algunas tareas que podemos hacer o no con los índices virtuales:

1- Alterarlos

SQL> alter index VIRTUAL_PRUEBA rebuild;

ERROR at line 1:
ORA-08114: can not alter a fake index

SQL> alter index VIRTUAL_PRUEBA storage(pctincrease 0);

ERROR at line 1:
ORA-08114: can not alter a fake index

2- Intentar transformarlos en índices reales

SQL> create index VIRTUAL_PRUEBA on table_name(col1);

ERROR at line 1:
ORA-00955: name is already used by an existing object

Esto ocurre dado que los índices virtuales generan entradas en algunas tablas del diccionario de datos.
La solución en estos casos es eliminar el índice virtual, y crear el físico

SQL> drop index VIRTUAL_PRUEBA;

Index dropped.

SQL> create index VIRTUAL_PRUEBA on am301(col1);

Index created.

Siguiendo con el tema, lo que si podemos hacer es crear un índice virtual afectando a ciertas columnas de una tabla y por otro lado crear un índice físico (con otro nombre) pero que también afecte a las mismas columnas.

SQL> create index VIRTUAL on emp(col1, col2, col3) nosegment;

Index created.

SQL> create index FISICO on emp(col1, col2, col3);

Index created.

Una vez que terminaron de trabajar con estos índices se recomienda eliminarlos.

Espero que les haya servido.

Saludos!
Gondalf