par Guillaume Lelarge.

Utiliser les fonctions de fenêtrage pour une pagination efficace


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, seules les bases de données SQL Server et Oracle peuvent les utiliser pour une requête top-N en pipeline. PostgreSQL n'annule pas le parcours d'index après avoir récupéré les lignes nécessaires, ce qui implique qu'elle ne peut donc pas les exécuter de façon efficace. MySQL ne connaît pas les fonctions de fenêtrage.

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 :

---------------------------------------------------------------
|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)

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.

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.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license