Indexar Order By

Las sentencias SQL con el filtro order by no necesitan ordenar el resultado de manera explícita si el índice relevante entrega las filas en el orden requerido. Eso significa que el mismo índice que es utilizado para el filtro where debe también cubrir la clausula order by.

Como ejemplo, se considera la siguiente sentencia que selecciona las ventas del día de ayer ordenada por datos de venta e identificador (ID) de producto:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

Existe un índice sobre SALE_DATE que puede utilizarse para el filtro where. Sin embargo, la base de datos debe realizar una operación de ordenación explícita para cumplir con la cláusula order by:

De todas formas, un INDEX RANGE SCAN entrega el resultado en el orden del índice. Para aprovecharse de esto, basta con extender la declaración del índice para que coincida con la cláusula order by:

  DROP INDEX sales_date

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id)

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

La operación de ordenación SORT ORDER BY desapareció del plan de ejecu­ción aunque la sentencia tiene todavía una cláusula order by. La base de da­tos explota el orden del índice y omite la operación explícita de ordenación.

A pesar de que el nuevo plan de ejecución tiene pocas operaciones, el coste se ha incrementado considerablemente porque el factor de agrupación del nuevo índice es peor (ver “Optimizar automáticamente el factor de agru­pa­ción”). En ese momento, hay que señalar que el coste no es siempre un buen indicador del esfuerzo de ejecución.

Para esta optimización, basta con que el rango escaneado del índice se ordene de acuerdo con la cláusula order by. De este modo, la optimización también funciona para este caso particular cuando se ordena solamente por PRODUCT_ID only:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

En la Figura 6.1, se puede ver que la columna PRODUCT_ID es el único criterio de ordenación relevante dentro del rango escaneado del índice. De ahí, el orden del índice coincide con la cláusula del order by dentro del rango del índice así que la base de datos puede omitir la operación de ordenación.

Esta optimización puede causar un comportamiento inesperado cuando se extiende el rango escaneado del índice:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

Esta sentencia no recupera las ventas de ayer sino todas las ventas desde ayer. Eso significa que cubre varios días y escanea un rango del índice que no está ordenado únicamente por el PRODUCT_ID. Si se observa nuevamente la Figura 6.1 y se extiende el rango escaneado del índice hasta la parte inferior, se puede ver que existen nuevamente valores más pequeños de PRODUCT_ID. Por lo tanto, la base de datos debe usar una operación de ordenación explícita para satisfacer la cláusula order by.

Si la base de datos usa una operación de ordenación aunque se espera una ejecución en pipeline, puede ser por dos razones: (1) el plan de ejecución con la operación de ordenación explícita tiene menor coste; (2) el orden del índice dentro del rango escaneado del índice no corresponde a la cláusula order by.

Una manera sencilla para distinguir estos dos casos especiales es usar la declaración completa del índice dentro de la cláusula order by; eso se hace ajustando la sentencia al índice con el fin de eliminar la segunda causa. Si la base de datos emplea todavía una operación de ordenación explícita, el optimizador prefiere este plan debido al valor de su coste; de lo contrario, la base de datos no puede utilizar el índice para la cláusula order by original.

En ambos casos, se preguntarán si se podría obtener una ejecución en pipeline del order by y cómo. Para ello, se podría ejecutar la sentencia con la declaración completa del índice dentro de la clausula order by y después analizar el resultado. Generalmente, esto revela una percepción equivocada del índice, que el orden del índice no es el que necesita la cláusula original del order by, así que la base de datos no puede utilizar el índice para evitar una operación de ordenación.

Si el optimizador prefiere una operación de ordenación explícita para su coste, es generalmente porque el optimizador toma el mejor plan de ejecución para la ejecución completa de la sentencia. En otras palabras, el optimizador opta para el plan de ejecución más rápido para obtener el último registro. Si la base de datos detecta que la aplicación busca solamente las primeras filas, se podría priorizar a su vez un order by indexado. Capítulo 7, “Resultados parciales”, explica los métodos de optimización correspondientes.

Si te gusta mi manera de explicar, te encantará mi libro.