TOP FIVE de casos en los cuales los índices que creamos 'inexplicablemente' no funcionan.
1 - CONSULTAS QUE RETORNAN MUCHOS REGISTROS
Este es el caso de consultas que retornan muchos registros. Según algunos autores, más del 5 por ciento del total de filas, pero en realidad es un porcentaje variable que se infiere según varios parámetros como cardinalidad, cantidad de filas, distribución en los bloques, etc.
En este tipo de operaciones, es mas 'barato' para la base de datos leer toda la tabla y colocarla en memoria que acceder a cada registro mediante el índice. Hay que pensar que un índice es una estructura más que debemos consultar para recién llegar al bloque que contiene nuestro registro y traerlo a memoria, por lo cual ese acceso trae un costo adicional para cada fila. Si consultamos pocos registros, el incremento del costo por acceso a índice se absorve con el tiempo total. Si consultamos muchos registros, el overhead incrementa significativamente el tiempo de completitud.
No está mal que los índices no se usen y se produzcan 'FULL SCAN TABLES'. El optimizador sabe los elementos que tiene a mano, cual es el costo asociado a cada método y por lo tanto puede resolver qué usar en cada caso.
2 - SE APLICAN FUNCIONES SOBRE COLUMNAS INDIZADASCuando aplicamos cualquier función en una columna indizada en la cláusula WHERE, el índice deja de tener efecto.
Un simple TRUNC sobre una columna fecha, o un TRIM en una columna VARCHAR2, deja sin efecto a los índices sobre esas columnas.
SELECT nombre, TRIM(apellido)
FROM socios
WHERE SUBSTR(codigo,1,3) = 'DNO'
AND apellido > 'P';
En este ejemplo, un índice sobre la columna codigo no es de utilidad, ya que la función SUBSTR lo anula. Un índice sobre apellido puede funcionar sin problemas ya que no hay ninguna función aplicada en la cláusula WHERE (notar que sí la hay en el SELECT).
3 - CONVERSIONES IMPLICITASPor efecto del punto anterior, hay algunos casos en los que no tenemos en cuenta los tipos de datos en el WHERE, y dejamos que se realicen conversiones automáticas. Esto no es preferible, ya que Oracle siempre convierte la columna al tipo del elemento comparante.
Por ejemplo, una consulta como la siguiente:
SELECT nombre, TRIM(apellido)
FROM socios
WHERE nro_socio = '2232';
internamente es traducida como:
SELECT nombre, TRIM(apellido)
FROM socios
WHERE TO_CHAR(nro_socio) = '2232';
...y el índice sobre nro_socio es inhibido por la conversión implícita.
Siempre es bueno convertir explícitamente este tipo de condiciones en donde intervienen diferentes tipos de datos.
Otras opciones son: estandarizar el almacen de datos (por ejemplo guardando siempre los valores con la función aplicada), usar una función de conversión sobre el literal en lugar de en la columna, crear índices de función.
4 - EL PREDICADO NO ES ADECUADOPor ejemplo, tenemos un índice compuesto formado por las columnas a, b y c. Se quiere realizar una consulta incluyendo en la cláusula WHERE la condicion b (por la cual queremos usar el índice), pero no se incluye a!
Aunque la columna a almacene el mismo valor para todos los registros, Oracle necesita explícitamente todas las columnas previas entre las condiciones del where. De la misma manera, si queremos usar una condición sobre c, deberíamos incluir en el where condiciones para a y b.
Otro caso es cuando tenemos un OR en el where, un distinto != sobre la columna indizada, o cuando la columna aparece a ambos lados de una expresión.
5 - ESTADISTICAS OBSOLETAS (O INEXISTENTES)El optimizador de la base de datos construye un plan de ejecución basado enteramente en estadísticas de los objetos, almacenadas en el dicionario de datos. La recolección de estas estadísticas debe realizarse periódicamente para que Oracle sepa cuales son los costos asociados a cada método de acceso a los registros, y de esta manera elegir la mejor opción. Al insertar y eliminar datos frecuentemente, cambian las estadísticas quedan obsoletas, y esto puede traer aparejado un impacto directo en la performance de las consultas.
Pueden recolectarse estadísticas de todo el esquema, de una tabla, de un índice, del sistema. El paquete DBMS_STATS contiene procedimientos para hacer el trabajo: GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_SCHEMA_STATS.
Para saber si una tabla tiene estadísticas, puede utilizarse la siguiente consulta:
SELECT last_analyzedFROM user_tablesWHERE table_name = 'mi_tabla';Retorna la fecha de la ultima recolección. Si retorna NULL, entonces nunca fueron recolectadas.Ver también:Indices condicionales11g y sus índices invisiblesIndices de función para mejorar un LIKE
