de Martin LE TARNEC.

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 ejecución aunque la sentencia tiene todavía una cláusula order by. La base de datos explota el orden del índice y omite la operación explícita de ordenación.

Importante

Si el orden del índice cumple con la cláusula del order by, entonces la base de datos puede omitir 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 agrupación). En ese momento, hay que señalar que el coste no es siempre un buen indicador del esfuerzo de ejecución.

Optimizar automáticamente el factor de agrupación

La base de datos Oracle mantiene el factor de agrupación como mínimo considerando el ROWID para el orden del índice. Siempre que dos entradas del índice tengan el mismo valor de llave, el ROWID determina el orden final. Por lo tanto, el índice también está ordenado de acuerdo con el orden de la tabla y tendrá el factor de agrupación más pequeño posible porque el ROWID representa la dirección física de la fila de la tabla.

Si se agrega otra columna a un índice, se insertan nuevos criterios de ordenación antes del ROWID. La base de datos tiene menos espacio libre ajustando las entradas del índice con el orden de la tabla; así que el factor de agrupación solamente puede empeorar.

Independientemente, todavía es posible que el orden del índice coincida casi con el orden de la tabla. Las ventas del día probablemente están agrupadas juntas dentro de la tabla así como dentro del índice, aunque su secuencia ya no sea exactamente la misma. La base de datos tiene que leer los bloques de la tabla en varias lecturas cuando usa el índice SALE_DT_PR, aunque éstos son los mismos bloques que antes. Gracias a la caché de datos accedidos con frecuencia, el impacto sobre el rendimiento puede ser considerablemente bajo, como indican los valores de coste.

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 , 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.

Figura 6.1 El orden de clasificación dentro del rango importante del índice

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.

Sugerencia

Usar la declaración completa del índice dentro de la cláusula order by para encontrar la razón de una operación de ordenación explícita.

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.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia