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, Oracle y PostgreSQL 15+ las pueden utilizar para la sentencia top-N en pipeline. MySQL, MariaDB0, Db2 y PostgreSQL no abortan el escaneo del índice después de recuperar los registros necesarios y, por lo tanto, ejecutan la sentencia ineficazmente.
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 DESCLa 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 (LUW)
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 <= 20no 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)- PostgreSQL
Desde la versión 15, el plan de ejecución muestra “Run condition”, las cuales pueden cancelar la ejecución de las ultimas etapas.
QUERY PLAN ------------------------------------------------------- Subquery Scan on tmp (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1) Filter: (tmp.rn >= 11) Rows Removed by Filter: 10 Buffers: shared hit=5 -> WindowAgg (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1) Run Condition: (row_number() OVER (?) <= 20) Buffers: shared hit=5 -> Index Scan Backward using sl_dtid on sales (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1) Buffers: shared hit=5
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 soporte de esta optimización no es muy común en los productos SQL.
- Solo con
ROW_NUMBER() - No con la clausura
PARTITION BY(ver abajo)
Mientras la actualización podría ejecutarse con cualquier función monotona, existe un enfoque en la funcion ROW_NUMBER entre las implementaciones analizadas.
- Solo con la clausura
where:OVER(ORDER BY…)+WHERE x=?
Un cuidado debe aplicarse cuando se use PARTITION BY: aunque la clausura WHERE limita los registros hacia una sola partición, el hecho de usar PARTITION BY desactiva la optimización de algunos productos. Eso sucede si la función de ventana particionada es parte de una vista, porque la salida de la sentencia SQL limita la vista a una sola partición.
- No todas las funciones funcionan con la clausura
WHERE
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.
