Oracle Database 19c DBMS_AUTO_INDEX.drop_secondary_indexes

Oracle Database 19c E.E. para Ingienería de Sistemas, introduce la función de indexación automática, que le permite al motor de base de datos tomar algunas decisiones sobre la administración de los índices en la base de datos.

La función de indexación automática hace lo siguiente.

• Identifica índices automáticos potenciales basados en el uso de la columna de la tabla. La documentación llama a estos "índices candidatos".
• Crea índices automáticos como índices invisibles, para que no se utilicen en los planes de ejecución. Los nombres de índice incluyen el prefijo "SYS_AI".
• Prueba los índices automáticos invisibles con las sentencias SQL para asegurarse de que ofrecen un rendimiento mejorado.
• Si los índices creados, dan como resultado un rendimiento mejorado, se hacen visibles. Si no se mejora el rendimiento, el índice automático relevante se marca como inutilizable y luego se elimina.
• Las sentencias SQL probadas contra índices automáticos fallidos se incluyen en la lista negra, por lo que no se considerarán para la indexación automática en el futuro.
• El optimizador no considera los índices automáticos la primera vez que se ejecuta el SQL en la base de datos.
• Eliminar índices no utilizados.

Como bien lo indiqué al inicio, esto es sólo posible en equipos de Ingeniería de Software de Oracle, o sea, Exadata, sin embargo, en un ambiente de pruebas, podemos utilizar el parámetro "_exadata_feature_on"=true, para simular esta condición especial.

Lo que hace interesante al paquete DBMS_AUTO_INDEX, es uno de sus parámetros de ejecución: "drop_secondary_indexes".

En mis charlas, en distintas ocasiones cuando me preguntaban sobre recomendaciones para migrar de versiones legadas de base de datos a versiones más actualizadas -sobre todo si se estaba haciéndolo de una versión 10g o inferior a 11g o 12c- me gustaba recomendar el borrar todos aquellos índices que no estuvieran involucrados con las políticas de restricción de PK y FK. Esto sobre todo, por el tema del optimizador de consultas, que a partir de la versión 11g, sólo implementa optimización basada en "Costo" y que el tema de los índices superflúos, era un gran dolor de cabeza.

Este parámetro para el paquete DBMS_AUTO_INDEX, elimina de la base de datos, todos aquellos índices que no cumplan con la condición anteriormente brindada, con el fin de que la característica de autonomía de creación de índices, pueda de manera eficiente, determinar que índices se requieren para las consultas ejecutadas en una base de datos y que no sean satisfechas a través de los índices creados paralelamente para los constraints o políticas de restricción PK y FK.

Veamos esto con un pequeño ejemplo.

Tenemos una tabla creada en una instancia de 19c, en un usuario común y silvestre.

SQL> connect user_test/oracle@source
Connected.

SQL> desc datos
 Name                         Null?    Type
 ---------------------------- -------- ---------------
 EMPLOYEE_ID                           NUMBER(6)
 FIRST_NAME                            VARCHAR2(20)
 LAST_NAME                    NOT NULL VARCHAR2(25)
 EMAIL                        NOT NULL VARCHAR2(25)
 PHONE_NUMBER                          VARCHAR2(20)
 HIRE_DATE                    NOT NULL DATE
 JOB_ID                       NOT NULL VARCHAR2(10)
 SALARY                                NUMBER(8,2)
 COMMISSION_PCT                        NUMBER(2,2)
 MANAGER_ID                            NUMBER(6)
 DEPARTMENT_ID                         NUMBER(4)

Esta tabla no tiene índices en este momento. Vamos a crear un índice sobre la columna "employee_id".

SQL> create index idx01_prueba_borrado on datos ( employee_id );

Index created.

Si hacemos al consulta en el diccionario de la base de datos, ahora aparece el índice creado anteriormente asociado a la tabla "Datos" en el esquema "USER_TEST".

SQL> connect system/oracle@source
Connected.

SQL> col owner format a20
SQL> col index_name format a40

SQL> select owner, index_name from dba_indexes
  2  where table_name='DATOS' and owner='USER_TEST';
/

OWNER                INDEX_NAME
-------------------- ----------------------------------------
USER_TEST            IDX01_PRUEBA_BORRADO

Para tener todo configurado para utilizar la característica de autonomía de creación de índices de la base de datos, es necesario ejecutar primero el siguiente paso:

SQL> EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('USER_TEST');

PL/SQL procedure successfully completed.

Al ejecutar nuevamente la consulta sobre los índices existentes en la tabla "DATOS" del esquema indicado, ahora ya no aparece dicho índice.

SQL> select owner, index_name from dba_indexes
  2  where table_name='DATOS' and owner='USER_TEST';

no rows selected

SQL>

Una vez más el tiempo, nos da la razón. Todo cae por su propio peso.