de Martin LE TARNEC.

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.

DB2
Explain Plan
---------------------------------------------------------------------
ID | Operation                      |                     Rows | Cost
 1 | RETURN                         |                          |  688
 2 |  FETCH SALES                   |     394 of 394 (100.00%) |  688
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 3 - STOP ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)

En DB2, los escaneos opuestos se pueden prevenir usando la clausula DISALLOW REVERSE SCAN durante la creación del índice.

Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  300 |
| 1 | TABLE ACCESS BY INDEX ROWID | SALES       |  320 |  300 |
|*2 |  INDEX RANGE SCAN DESCENDING| SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

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.

Figura 6.2 Escaneo opuesto del índice

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.

Importante

Las bases de datos pueden leer índices en ambos sentidos.

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.

Figura 6.3 Order by en pipeline imposibles

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.

DB2
Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  675
 2 |  FETCH SALES         |     387 of 387 (100.00%) |  675
 3 |   IXSCAN SALES_DT_PR | 387 of 1009326 (   .04%) |   24

Predicate Information
 3 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  301 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  301 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

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.

Importante

Cuando se usan los modificadores ASC y DESC mezclados dentro de la cláusula order by se debe definir el índice en el orden para utilizarlo durante un order by en pipeline.

Eso no afecta el uso del índice para el filtro where.

Figura 6.4 Orden del índice mezclado

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 2017 ni en MySQL 5.7. 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 12c. 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.

Figura 6.5 Base de datos/matriz de características

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