Les fonctions de fenêtrage offrent un autre moyen d'implémenter la pagination en SQL. C'est une méthode flexible et, par-dessus tout, c'est une méthode proposée par le standard. Néanmoins, seuls SQL Server, Oracle et PostgreSQL (à partir de la version 15 pour ce dernier) peuvent les utiliser pour une requête top-N en pipeline. MySQL, MariaDB0, et Db2 (LUW) n'annulent pas le parcours d'index après avoir récupéré les lignes nécessaires, ce qui implique qu'ils ne peuvent donc pas les exécuter de façon efficace.
L'exemple suivant utilise la fonction de fenêtrage ROW_NUMBER pour une requête de pagination :
SELECT *
FROM ( SELECT ventes.*
, ROW_NUMBER() OVER (ORDER BY date_vente DESC
, id_vente DESC) rn
FROM ventes
) tmp
WHERE rn between 11 and 20
ORDER BY date_vente DESC, id_vente DESC;La fonction ROW_NUMBER énumère les lignes suivant
l'ordre de tri défini dans la clause over. La clause
where externe utilise cette énumération pour limiter
les résultats à la seconde page (lignes 11 à 20).
La base de données Oracle reconnaît la condition d'annulation et
utilise l'index sur DATE_VENTE et ID_VENTE pour
produire un comportement du type requête top-N en pipeline :
- 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 | VENTES | 1004K| 36877 | | 4 | INDEX FULL SCAN DESCENDING | VT_DTID | 1004K| 2955 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=11 AND "RN"<=20) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "DATE_VENTE" DESC, "ID_VENTE" DESC ) <=20)- PostgreSQL
Depuis la version 15, le plan d'exécution affiche la « Run Condition », qui peut annuler l'exécution du reste.
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
L'opération WINDOW NOSORT STOPKEY indique qu'il n'y a
pas d'opération de tri (NOSORT) et que la base de données annule l'exécution une fois
qu'elle a atteint la limite supérieure (STOPKEY). En considérant que les opérations annulées ont été
exécutées sous la forme d'un pipeline, cela signifie que cette requête est
aussi efficace que la méthode de décalage expliquée dans la section précédente.
Le support de cette optimisation n'est pas du tout commun parmi les produits SQL.
- Seulement avec
row_number() - Sans la clause
partition by(voir ci-dessous)
Bien que cette optimisation pourrait fonctionner conceptuellement avec toute fonction monotone,
il y a clairement un focus sur la fonction ROW_NUMBER parmi les implémentations
analysées.
- Seulement avec la clause
where:OVER(ORDER BY…)+WHERE x=?
Il est nécessaire de faire attention lors de
l'utilisation de partition by : même si la clause where
limite les lignes à une seule partition, la seule présence de partition by
désactive cette optimisation dans certains produits. Ceci peut arriver si la fonction de
fenêtrage partitionnée fait partie d'une vue, mais que la requête englobante limite la vue à
une seule partition.
- Pas toutes les fonctions qui travaillent en présence d'une clause
where
Néanmoins, la force des fonctions de fenêtrage n'est pas la pagination mais les calculs analytiques. Si vous n'avez jamais utilisé de fonctions de fenêtrage auparavant, vous devriez passer quelques heures à étudier la documentation relative.
