de Martin LE TARNEC.

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.

Sugerencia

Informar a la base de datos cada vez que no se requieren todos los registros.

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.

DB2

DB2 soporta la sintaxis estándar fetch first desde la versión 9 por lo menos para LUW y zOS.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

La palabra reservada limit está soportada desde DB2 LUW 9.7 (requiere db2set DB2_COMPATIBILITY_VECTOR=MYS).

MySQL

MySQL y PostgreSQL usan la cláusula limit para restringir el número de registros devueltos.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10
Oracle

La base de datos Oracle introdujo el comando fetch first con su versión 12c. Con las versiones anteriores, se tenía que usar una seudo columna ROWNUM que contaba automáticamente el número de registros del resultado. Para usar esta columna dentro de un filtro, hay que encapsularla dentro de la sentencia:

SELECT *
  FROM (
       SELECT *
         FROM sales
        ORDER BY sale_date DESC
       )
 WHERE rownum <= 10
PostgreSQL

PostgreSQL soporta la extensión fetch first desde la versión 8.4. La cláusula anterior limit sigue funcionando como se ha demostrado en el ejemplo de MySQL.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY
SQL Server

SQL Server provee la cláusula top para restringir el número de registros devueltos.

SELECT TOP 10 *
  FROM sales
 ORDER BY sale_date DESC

Desde la versión 2012, SQL Server soporta también la extensión fetch first .

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

Importante

La base de datos solamente puede optimizar una sentencia para un resultado parcial si conoce esta intención desde el principio.

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

DB2
Explain Plan
-----------------------------------------------------------------
ID | Operation                      |               Rows |   Cost
 1 | RETURN                         |                    |     24
 2 |  FETCH SALES                   |      10 of 1009326 | 458452
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 1009326 of 1009326 |   2624

Predicate Information

El comportamiento top-N no es visible directamente en el plan de ejecución de DB2 a menos que haya una operación SORT requerida (la vista last_explained lo indicaría entre paréntesis: SORT (TOP-N); ver en el siguiente ejemplo).

En este ejemplo, uno podría sospechar que debe ser una sentencia top-N debido a la caída repentina de la estimación del número de registros que no se podría explicar por ningún predicados de filtro (la información de los predicados está vacía).

Oracle
-------------------------------------------------------------
| Operation                     | Name        | Rows | Cost |
-------------------------------------------------------------
| SELECT STATEMENT              |             |   10 |    9 |
|  COUNT STOPKEY                |             |      |      |
|   VIEW                        |             |   10 |    9 |
|    TABLE ACCESS BY INDEX ROWID| SALES       | 1004K|    9 |
|     INDEX FULL SCAN DESCENDING| SALES_DT_PR |   10 |    3 |
-------------------------------------------------------------

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.

Sugerencia

El Apéndice A, “Execution Plans, resume las operaciones correspondientes a DB2, MySQL, Oracle, PostgreSQL y SQL Server.

Importante

El empleo de una sentencia top-N en pipeline no necesita leer y ordenar el resultado entero.

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.

Figura 7.1 Escalabilidad de las sentencias Top-N

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.

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