Indexar ASC, DESC y NULLS FIRST/LAST

Las bases de datos pueden leer los índices en ambos sentidos. Eso significa que un order by en pipeline también es posible si el rango escaneado del índice está exactamente en el orden opuesto al especificado por la cláusula order by. Aunque los modificadores ASC y DESC dentro de la cláusula order by pueden evitar una ejecución en pipeline, la mayoría de las bases de datos ofrecen una manera sencilla para cambiar el orden del índice así que un índice llega a ser utilizable por un order by en pipeline.

El siguiente ejemplo usa un índice en el orden inverso lo que entrega las ventas realizadas desde ayer en orden descendente según la fecha y el PRODUCT_ID.

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

El plan de ejecución muestra que la base de datos lee el índice en el sentido descendente.

En este caso, la base de datos usa el árbol del índice para encontrar la última entrada correspondiente. Desde este momento, se sigue la cadena de los nodos hoja “hacia arriba” como se muestra en la Figura 6.2. De todos modos, eso es porque la base de datos utiliza una lista doblemente enlazada para construir la cadena del nodo hoja.

Por supuesto, es crucial que el rango escaneado del índice sea exactamente en el orden opuesto a como se necesita para la cláusula order by.

El siguiente ejemplo no cumple con los prerequisitos porque se mezclan los modificadores ASC y DESC dentro de la cláusula order by:

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

La sentencia debe entregar primero las ventas de ayer ordenadas por PRODUCT_ID en orden descendente y después las ventas de hoy, nuevamente ordenadas por PRODUCT_ID en orden descendente. La Figura 6.3 ilustra este proceso. Para obtener las ventas en el orden requerido, la base de datos tendría que “saltar” durante el escaneo del índice.

Sin embargo, el índice no tiene relación entre las ventas de ayer con el PRODUCT_ID más pequeño y las ventas de hoy con el PRODUCT_ID más grande. Por lo tanto, la base de datos no puede utilizar el índice para evitar una operación de ordenación explícita.

Para casos como este, la mayoría de las bases de datos ofrecen un método sencillo para ajustar el orden del índice a la cláusula order by. Concretamente, eso significa que se pueden utilizar los modificadores ASC y DESC dentro de la declaración del índice:

  DROP INDEX sales_dt_pr

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC)

Ahora, el orden del índice corresponde a la cláusula order by, así que la base de datos puede omitir la operación de ordenación.

La Figura 6.4 muestra el nuevo orden del índice. El cambio de sentido de ordenación para la segunda columna permuta el sentido de las flechas en comparación con la ilustración anterior. Eso hace que la primera flecha termine donde empieza la segunda así que el índice tiene las filas en el orden deseado.

La indexación de ASC/DESC es necesaria sólo para ordenar columnas individuales que tienen un sentido opuesto. No es necesario invertir el orden de todas las columnas porque la base de datos puede leer el índice en orden descendente si se necesita; a excepción, claro, de los índices secundarios sobre “tablas ordenadas según un índice”. Los índices secundarios implícitos añaden una clave de agrupación al índice sin proveer ninguna posibilidad de especificar el orden (de ordenación). Si se necesita ordenar la clave de agrupación en orden descendente, no hay otra opción que ordenar todas las demás columnas de forma descendente. Así, la base de datos no puede leer el índice en sentido opuesto para obtener el orden deseado.

Además de ASC y DESC, el estándar SQL define dos modificadores poco conocidos para la cláusula order by: NULLS FIRST y NULLS LAST. El control explicito sobre el ordenamiento de NULL fue introducido “recientemente” como una extensión opcional con SQL:2003. Como consecuencia, el soporte de base de datos es escaso. Eso es particularmente preocupante porque el estándar no define exactamente el orden de ordenación de los NULL, que sólo especifica que todos los NULL deben aparecen juntos después de ordenar. Pero no especifica si deberían figurar antes o después de las otras entradas. Estrictamente hablando, se necesitaría realmente especificar el ordenamiento del NULL para todas las columnas que pueden ser NULL dentro de la cláusula order by para obtener el comportamiento correcto.

Sin embargo, la realidad es que la extensión opcional no está implementada en SQL Server 2019 ni en MySQL 8.0. La base de datos Oracle soporta el ordenamiento de los NULL desde antes de ser introducido en el estándar, pero no se aceptan dentro de las declaraciones de índice hasta la versión 19c. Por este motivo, la base de datos Oracle no puede utilizar un order by pipeline cuando se ordena con NULLS FIRST. Solamente la base de datos PostgreSQL (desde la versión 8.3) soporta el modificador de NULL en la cláusula order by y en la declaración del índice.

La siguiente perspectiva general resume las características proporcionadas por cada base de datos.

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