Seleccionar registros Top-N

Las sentencias Top-N limitan el resultado a un cierto número de registros. Son generalmente sentencias para obtener la “más reciente” o las “mejores” entradas del resultado. Para obtener ejecuciones eficientes, la clasificación debe hacerse con order by en pipeline.

La manera más sencilla para traer solamente los primeros registros de una sentencia es recuperar los datos requeridos y después cortar la sesión. Desafortunadamente, el optimizador no puede anticiparlo durante la preparación del plan de ejecución. Para seleccionar el mejor plan de ejecución, el optimizador ha de saber si la aplicación recuperará finalmente todos los registros o no. En este caso, un escaneo entero de la tabla con operaciones explícitas de ordenación puede tener buen rendimiento, aunque order by en pipeline podría ser mejor si devolviera solamente diez registros, incluso aunque la base de datos tenga que devolver cada registro de manera individual. Eso significa que el optimizador ha de saber si se va a abortar la sentencia antes de recuperar todos los datos para así seleccionar el mejor plan de ejecución.

Hace mucho tiempo, el estándar SQL excluía esta funcionalidad. La extensión correspondiente (fetch first) se presentó con SQL 2008 y actualmente solamente está disponible en IBM Db2, PostgreSQL, SQL Server 2012 y Oracle 12c. Esto es así porque la característica es una extensión “non-core”, y porque cada base de datos ha ofrecido su propia solución desde hace varios años.

El siguiente ejemplo muestra el uso de esas extensiones bien conocidas para seleccionar las diez ventas más recientes. El fundamento es siempre el mismo: recuperar todas las ventas, empezando por las más recientes. La sintaxis respectiva top-N aborta la ejecución después de devolver los diez registros.

Todas las sentencias SQL mostradas arriba son especiales porque las bases de datos las reconocen como sentencias top-N.

Si el optimizador es consciente del hecho de que solamente se requieren diez registros, preferirá usar el comando order by en pipeline si aplica:

El plan de ejecución Oracle indica el final planificado con el comando COUNT STOPKEY. Eso significa que la base de datos reconoce la sintaxis de la sentencia top-N.

Usar la sintaxis correcta es la solución sólo para la mitad del problema porque para terminar la ejecución de manera eficiente se requiere que las operaciones se ejecuten internamente en pipeline. Eso significa que la cláusula order by debe ser cubierta por un índice; en este ejemplo, el índice SALE_DT_PR sobre SALE_DATE y PRODUCT_ID. Al utilizar este índice, la base de datos puede evitar una operación de ordenación explícita y así enviar de inmediato los registros hacia la aplicación, porque se leen desde el índice. La ejecución se aborta después de recibir diez registros así que la base de datos ya no lee más registros de los que se seleccionaron.

Si no existe un índice apropiado sobre SALE_DATE para hacer un order by en pipeline, la base de datos debe leer y ordenar la tabla entera. Los primeros registros solamente se entregan después de haber leído el último registro de la tabla.

Este plan de ejecución no tiene order by en pipeline y es casi igual de lento que abortar la ejecución desde el lado del cliente. Usar la sintaxis top-N es aún mejor porque la base de datos no necesita materializar el resultado entero sino solamente los diez registros más recientes. Eso requiere mucha menos memoria. El plan de ejecución de Oracle indica esta optimización con la palabra clave STOPKEY sobre la operación SORT ORDER BY.

Las ventajas de una sentencia top-N en pipeline incluyen no solamente una mejora de rendimiento de inmediato, sino también una mejora de la escalabilidad. Sin usar la ejecución en pipeline, el tiempo de respuesta de estas sentencias top-N crece con el tamaño de la tabla. Sin embargo, en pipeline, el tiempo de respuesta crecerá solamente con el número de registros seleccionados. En otras palabras, el tiempo de respuesta de una sentencia top-N en pipeline es siempre el mismo; es casi independiente del tamaño de la tabla. Solamente cuando la profundidad del índice B-tree crece la sentencia llega a ser un poco más lenta.

La Figura 7.1 muestra la escabilidad para ambas variantes por frente al crecimiento del volumen de datos. Se puede ver claramente que el crecimiento del tiempo de respuesta es lineal para una ejecución sin order by en pipeline. El tiempo de respuesta para una ejecución en pipeline permanece constante.

Aunque el tiempo de respuesta de la sentencia top-N en pipeline no depende del tamaño de la tabla, sí crece con el número de registros seleccionados. Por lo tanto, el tiempo de respuesta se verá duplicado cuando se seleccionen el doble de registros. Esto resulta particularmente significante para las sentencias de paginación que cargan adicionalmente resultados porque generalmente esas sentencias empiezan nuevamente desde la primera entrada; se leen los registros como se despliegan en la página anterior y finalmente se rechazan antes de alcanzar los resultados para la segunda página. Sin embargo, existe una solución para este problema que se explicará en la próxima sección.

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