mercredi 27 juin 2012

Afficher le "Top N" d'une table

Il nous est tous arrivé au moins une fois d'avoir à écrire une requête ayant pour but d'afficher le Top N d'une table ou d'un résultat de requêtes (par exemple afficher les 10 plus gros salaires de sa société ;-) ).

J'ai pensé à écrire un article sur ce sujet car j'ai vu récemment un développeur qui, pour afficher les 20 plus récentes lignes d'une table, effectuait un SELECT de toute la table avec un tri décroissant sur le champ DATE puis au niveau applicatif ne récupérait que les 20 premières lignes qui l'intéressaient. Cette manière de procéder est justement celle à proscrire car elle implique un tri de toute la table. Heureusement, pour mon développeur la table ne contenait que quelques milliers de lignes mais imaginez une table contenant plusieurs millions de lignes, ça engendrerait surement un tri sur disque (swap sur tablespace temporaire car pas assez de mémoire).

La bonne approche dans Oracle consiste à utiliser la pseudo-colonne ROWNUM de la manière suivante:

select * from (select * from ma_table order by champ_date desc) where rownum <=20;

L'idée consiste à appliquer le ROWNUM sur une vue en ligne contenant la table triée par le champ date.
L'erreur à ne pas commettre est la suivante:

select * from ma_table  where rownum <=20 order by champ_date desc;
En effet, ici le ROWNUM est appliqué avant le ORDER BY, du coup on obtient les 20 premières lignes de la table récupérées par Oracle qui sont ensuite triées par le champ DATE.

Illustrons ce concept avec un exemple.

SQL> create table t1 as select * from all_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.
J'ai une table T1 contenant toutes les lignes de la vue ALL_TABLES.
Je souhaiterais afficher les 5 tables contenant le plus de lignes.
La bonne solution est la suivante:

SQL>  select * from
  2  (select table_name,num_rows from T1 order by 2 desc NULLS  LAST)
  3  where rownum<=5;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SALES                              918843
SALES_TRANSACTIONS_EXT             916039
WRI$_OPTSTAT_HISTGRM_HISTORY       710550
SOURCE$                            636698
WRH$_SYSMETRIC_HISTORY             250920

5 rows selected.

------------------------------------------------------------------------------------------ | Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT        |      |      1 |        |      5 |00:00:00.01 |     164 | |   1 |  COUNT STOPKEY          |      |      1 |        |      5 |00:00:00.01 |     164 | |   2 |   VIEW                  |      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   3 |    SORT ORDER BY STOPKEY|      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   4 |     TABLE ACCESS FULL   | T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | ------------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets         164  consistent gets           0  physical reads           0  redo size         647  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)           5  rows processed

Voyons ce que donne la requête si je filtre sur le ROWNUM avant l'ORDER BY:

SQL> select table_name,num_rows
  2  from T1  where rownum<=5
  3  order by 2 desc NULLS  LAST;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
CCOL$                               20566
CDEF$                               17426
SEG$                                 7761
MLOG$                                   0
UET$                                    0

Ce n'est clairement pas le résultat souhaité.

Pour la bonne requête j'ai affiché son plan d'exécution ainsi que les statistiques d'exécution fournies par AUTOTRACE. Comparons ces stats avec la requête qui consiste à récupérer toutes lignes triées pour ne filtrer que le top N côté client (celle utilisée par mon développeur):

select table_name,num_rows from T1 order by 2 desc NULLS  LAST;

------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |   4542 |00:00:00.01 |     164 | |   1 |  SORT ORDER BY     |      |      1 |   4542 |   4542 |00:00:00.01 |     164 | |   2 |   TABLE ACCESS FULL| T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | -------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------         726  recursive calls           0  db block gets         303  consistent gets         165  physical reads           0  redo size      145436  bytes sent via SQL*Net to client        3737  bytes received via SQL*Net from client         304  SQL*Net roundtrips to/from client          27  sorts (memory)           0  sorts (disk)        4542  rows processed
On constate qu'avec la bonne approche on retourne beaucoup moins de lignes à l'application cliente (5 vs 4542), que le nombre de logical reads est moins important (164 vs 303) et qu'on génère moins de tris (1 vs 27). La différence en termes de performance aurait été plus significative si la table était beaucoup plus volumineuse.

La différence sur le nombre de tris s'explique par l'utilisation de l'opération SORT ORDER BY STOPKEY qui permet de ne trier que les 5 lignes. D'ailleurs la colonne A-ROWS du plan d'exécution pour cette opération donne un chiffre de 5.Je rappelle que cette colonne du plan indique le nombre de ligne réellement processées pour chaque opération du plan. Tom Kyte dans son livre Effective Oracle By Design explique très bien comment Oracle, grâce à l'utilisation d'un tableau en mémoire, arrive à ne trier que N lignes au lieu de toutes les lignes de la table (après application des éventuels filtres dans la clause WHERE).
Voici ce que dit Tom Kyte dans son livre:
The first N rows will populate this array of rows in sorted order. When the N+1 row is fetched,
it will be compared to the last row in the array. If it would go into slot N+1 in the array, it gets
thrown out. Otherwise, it is added, sorted to this array, and one of the existing rows is discarded.
Our sort area holds N rows maximum, so instead of sorting one million rows, we sort N rows.
This seeming small detail of using an array concept and just sorting N rows can lead to huge
gains in performance and resource usage. It takes a lot less RAM to sort ten rows than it does to sort one million rows (not to mention TEMP space usage!).

Une autre manière d'afficher le Top N consiste à utiliser la fonction analytique RANK (ou DENSE_RANK):

SQL> select * from
  2  (
  3  select table_name,num_rows, dense_rank() over (order by num_rows desc nulls last) DR
  4  from T1
  5  ) where DR <=5;

TABLE_NAME                       NUM_ROWS         DR
------------------------------ ---------- ----------
SALES                              918843          1
SALES_TRANSACTIONS_EXT             916039          2
WRI$_OPTSTAT_HISTGRM_HISTORY       710550          3
SOURCE$                            636698          4
WRH$_SYSMETRIC_HISTORY             250920          5

------------------------------------------------------------------------------------------- | Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |      |      1 |        |      5 |00:00:00.01 |     164 | |   1 |  VIEW                    |      |      1 |   4542 |      5 |00:00:00.01 |     164 | |   2 |   WINDOW SORT PUSHED RANK|      |      1 |   4542 |      6 |00:00:00.01 |     164 | |   3 |    TABLE ACCESS FULL     | T1   |      1 |   4542 |   4542 |00:00:00.01 |     164 | -------------------------------------------------------------------------------------------

Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets         164  consistent gets           0  physical reads           0  redo size         718  bytes sent via SQL*Net to client         415  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           1  sorts (memory)           0  sorts (disk)           5  rows processed


Aucun commentaire:

Enregistrer un commentaire