Estoy haciendo un SELECT sobre una tabla Ordenes el cual tiene un campo FechaOrden (La cual tiene un indice).

Al realizar un SELECT con filtro con un rango de fecha pequeño, el EXPLAIN PLAN me muestra que se usa el indice, pero si aumento el rango de la fecha (ejemplo de 10 dias a 60) no se usa el indice.

Porque motivo?

Gracias de antemano.

Saludos

Hola,

Esto no es un fallo, es el optimizador de Oracle (CBO, Cost-Based Optimizer) tomando una decisión consciente. Cuando el rango de fechas es pequeño, el optimizador estima que la consulta va a devolver pocas filas del total de la tabla, y ahí un rango por índice (leer el índice y después ir fila a fila a la tabla por rowid) sale más barato que recorrer la tabla entera.

Pero cuando amplias el rango a 60 días, el optimizador estima que ese filtro va a devolver un porcentaje mucho mayor de la tabla (según sus estadísticas de distribución de FechaOrden). A partir de cierto punto, hacer miles de saltos individuales al índice y luego a la tabla (una lectura de I/O aleatoria por cada fila) sale más caro que simplemente leer la tabla entera de forma secuencial (FULL TABLE SCAN), que es I/O secuencial y mucho más eficiente por fila. El umbral típico orientativo ronda entre el 2% y el 10-15% de las filas de la tabla, dependiendo de cómo estén distribuidos físicamente los datos.

Cosas que puedes comprobar o probar:

1. Estadísticas actualizadas. Si las estadísticas de la tabla están desactualizadas, el optimizador estima mal y puede tomar decisiones incorrectas en ambos sentidos. Actualízalas con:

EXEC DBMS_STATS.GATHER_TABLE_STATS('TU_ESQUEMA', 'ORDENES');

2. Ver el coste real, no solo si usa el índice o no. Que use FULL TABLE SCAN no significa automáticamente que vaya peor. Compara el Cost que muestra el propio EXPLAIN PLAN para ambos casos; si el full scan tiene coste menor, probablemente el optimizador acierta.

3. Índice cubriente (covering index). Si la consulta solo necesita columnas que ya están en el índice (o añadiendo las que falten con un índice compuesto), Oracle puede resolver la consulta leyendo solo el índice, sin ir a la tabla por cada fila, lo que cambia por completo el cálculo de coste a favor del índice incluso en rangos amplios.

4. Forzar el índice si sabes que te equivocas tú, no el optimizador (úsese con cuidado, solo si has comprobado que realmente es más rápido en la práctica):

SELECT /*+ INDEX(o idx_ordenes_fecha) */ *
FROM Ordenes o
WHERE FechaOrden BETWEEN :fecha_inicio AND :fecha_fin;

Antes de forzar el hint, mide los dos planes con datos reales. En muchos casos el optimizador acierta y el full scan es de verdad más rápido para rangos amplios, aunque nuestra intuición diga lo contrario porque «hay un índice, debería usarlo».

Un saludo,
David Carrero