de Martin LE TARNEC.

Utilizar las funciones de ventana para una paginación


Las funciones de ventana ofrecen otra manera de implementar la paginación en SQL. Son flexibles y sobre todo constituyen un método dentro de los estándares. Sin embargo, solamente las bases de datos SQL Server y Oracle las pueden utilizar para la sentencia top-N en pipeline. DB2 y PostgreSQL no abortan el escaneo del índice después de recuperar los registros necesarios y, por lo tanto, ejecutan la sentencia ineficazmente. MySQL directamente no soporta las funciones de ventana.

El siguiente ejemplo usa la función de ventana ROW_NUMBER para una sentencia de paginación:

SELECT *
  FROM ( SELECT sales.*
              , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                          , sale_id   DESC) rn
           FROM sales
       ) tmp
 WHERE rn between 11 and 20
 ORDER BY sale_date DESC, sale_id DESC

La función ROW_NUMBER cuenta los registros de acuerdo con el orden de clasificación definido en el filtro over. El filtro exterior where usa la lista para restringir el resultado de la segunda página (desde la fila 11 hasta la fila 20).

La base de datos Oracle reconoce la condición de detención y usa el índice sobre SALE_DATE y SALE_ID para producir el comportamiento de la sentencia top-N en pipeline:

DB2
Explain Plan
-------------------------------------------------------------
ID | Operation                   |               Rows |  Cost
 1 | RETURN                      |                    | 65658
 2 |  FILTER                     |  100933 of 1009326 | 65658
 3 |   FETCH SALES               | 1009326 of 1009326 | 65295
 4 |    IXSCAN (REVERSE) SL_DTID | 1009326 of 1009326 |  5679

Predicate Information
 2 - RESID (11 <= Q3.$C8)
     RESID (Q3.$C8 <= 20)

Por favor, sé consciente de que DB2 LUW 10.5 no ejecuta esta sentencia como una sentencia top-N. Aunque previene la operación de ordenación, se lee igualmente el índice entero; la ejecución no se aborta después de recuperar 20 filas.

Para obtener una sentencia top-N correcta abortando después de leer 20 filas, se debe "empaquetar" doblemente la sentencia para aplicar primero la condición de parada top-N y después filtrar las 10 primeras filas:

SELECT *
  FROM (SELECT *
          FROM (SELECT sales.*
                     , ROW_NUMBER() OVER (ORDER BY sale_date DESC
                                                 , sale_id   DESC) rn
                 FROM sales
               ) tmp
         WHERE rn <= 20
       ) tmp2
 WHERE rn > 10
 ORDER BY sale_date DESC, sale_id DESC;
Explain Plan
-----------------------------------------------------------------
ID | Operation                       |               Rows |  Cost
 1 | RETURN                          |                    |    21
 2 |  FILTER                         |            7 of 20 |    21
 3 |   FETCH SALES                   |      20 of 1009326 | 65352
 4 |    IXSCAN (REVERSE) SALES_DT_ID | 1009326 of 1009326 |  5736

Predicate Information
 2 - RESID (10 < Q3.$C8)

Recuerda que el filtro rn <= 20 no aparece en la sección de los predicados, lo que se ve reflejado en la estimación del número de registros.

Oracle
---------------------------------------------------------------
|Id | Operation                      | Name    | Rows |  Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT               |         | 1004K| 36877 |
|*1 |  VIEW                          |         | 1004K| 36877 |
|*2 |   WINDOW NOSORT STOPKEY        |         | 1004K| 36877 |
| 3 |    TABLE ACCESS BY INDEX ROWID | SALES   | 1004K| 36877 |
| 4 |     INDEX FULL SCAN DESCENDING | SL_DTID | 1004K|  2955 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=11 AND "RN"<=20)
2 - filter(ROW_NUMBER() OVER (
           ORDER BY "SALE_DATE" DESC, "SALE_ID" DESC )<=20)

La operación WINDOW NOSORT STOPKEY indica que no existe una operación de ordenación (NOSORT) y que la base de datos aborta la ejecución cuando alcanza el límite superior (STOPKEY). Considerando que las operaciones de detención son ejecutadas en pipeline, eso significa que la sentencia es tan eficiente como el método offset que se ha explicado en la sección anterior.

El poder de las funciones ventana no es la paginación sino sus cálculos analíticos. Si nunca antes ha empleado las funciones ventana, debería indudablemente dedicarle algunas horas para estudiar la documentación correspondiente.

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