Índices invisibles en Oracle Database 11g a partir del Release 1

Oracle 11g permite marcar los índices como invisibles. Los índices invisibles se mantienen como cualquier otro índice, pero el optimizador los ignora a menos que el parámetro OPTIMIZER_USE_INVISIBLE_INDEXES se establezca en TRUE a nivel de instancia o sesión. Los índices se pueden crear como invisibles usando la palabra clave INVISIBLE, y su visibilidad se puede alternar usando el comando ALTER INDEX.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;

La siguiente secuencia de comandos crea y llena una tabla, luego crea un índice invisible en ella.

CREATE TABLE ii_tab (
  id  NUMBER
);

BEGIN
  FOR i IN 1 .. 10000 LOOP
    INSERT INTO ii_tab VALUES (i);
  END LOOP;
  COMMIT;
END;
/

CREATE INDEX ii_tab_id ON ii_tab(id) INVISIBLE;

EXEC DBMS_STATS.gather_table_stats(USER, 'ii_tab', cascade=> TRUE);

Una consulta que usa la columna indexada en la cláusula WHERE ignora el índice y realiza un escaneo completo de la tabla.

SET AUTOTRACE ON
SELECT * FROM ii_tab WHERE id = 9999;

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

La configuración del parámetro OPTIMIZER_USE_INVISIBLE_INDEXES hace que el índice esté disponible para el optimizador.

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
SELECT * FROM ii_tab WHERE id = 9999;

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| II_TAB_ID |     1 |     3 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Hacer que el índice sea visible significa que todavía está disponible para el optimizador cuando se restablece el parámetro

OPTIMIZER_USE_INVISIBLE_INDEXES.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
ALTER INDEX ii_tab_id VISIBLE;

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| II_TAB_ID |     1 |     3 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Los índices invisibles pueden ser útiles para procesos con necesidades de indexación específicas, donde la presencia de los índices puede afectar negativamente a otras áreas funcionales. También son útiles para probar el impacto de eliminar un índice.

El estado de visibilidad actual de un índice se indica mediante la columna VISIBILITY de las vistas [DBA|ALL|USER]_INDEXES.