3 août 2009
1
03
/08
/août
/2009
14:34
Rechercher, en ordonnant sur une colonne indexée, les N premières lignes... un problème classique, et facile à résoudre !
Parmi les méthodes classique, il y a la limitation par ROWNUM bien sûr, ou son pendant "SQL Normé" : les fonctions analytiques.
On va s'intéresser ici à la méthode analytique et des petites difficultés qu'on peut rencontrer :
1) Utilisation de l'index :
a. La contrainte NOT NULL
b. Comportements 9i / 10g
c. Les tris sur colonnes alphanumériques
2) Cardinalité du TOP
Pour commencer, on va se créer un petit jeu de tests basé sur dba_objects :
SQL> CREATE TABLE test_top AS
2 SELECT *
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 /
Table créée.
SQL> CREATE INDEX test_top_ii ON test_top(object_id) COMPUTE STATISTICS
2 /
Index créé.
SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_TOP')
Procédure PL/SQL terminée avec succès.
1) a. Les valeurs NULL ne sont pas stockées dans l'index. Ainsi, lorsqu'on demande toutes les données ordonnées par une colonne définie en NULL, l'index ne peut être utilisé : (Illustration avec la méthode ROWNUM)
SQL> set autotrace traceonly explain
SQL> SELECT * FROM
2 (SELECT *
3 FROM test_top
4 ORDER BY object_id)
5 WHERE rownum <= 5
6 /
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | | 313 (2)|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 12879 | 2226K| | 313 (2)|
|* 3 | SORT ORDER BY STOPKEY| | 12879 | 1081K| 2952K| 313 (2)|
| 4 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51 (2)|
---------------------------------------------------------------------------------
Donc le premier pré-requis pour l'utilisation de l'index : avoir au moins une colonne NOT NULL !
SQL> ALTER TABLE test_top MODIFY object_id NOT NULL;
Table modifiée.
SQL> SELECT * FROM
2 (SELECT *
3 FROM test_top
4 ORDER BY object_id)
5 WHERE rownum <= 5
6 /
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | 3 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 5 | 885 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 3 (0)|
| 4 | INDEX FULL SCAN | TEST_TOP_II | 5 | | 2 (0)|
----------------------------------------------------------------------------------
On note au passage que le comportement recherché est bien INDEX FULL SCAN + STOPKEY : on lit l'index en commençant par la première feuille, et on limite la recherche. Pour une recherche de borne sur une colonne indexée non null, on aura de la même manière INDEX FULL SCAN (MIN/MAX)...
Autre remarque : pour contourner cette histoire de contrainte NOT NULL, j'ai tenté le ORDER BY NULL LAST... en vain (ce qui se comprend un peu, car même si les NULL sont en derniers, on ne peut affirmer avant exécution qu'ils ne sont pas dans le périmètre de la requête !)
1)b.
A présent, le vif du sujet, qui constitua pour moi une des surprises agréables de la 10g.
Voyons le résultat du la version analytique :
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM
4 (SELECT a.*, row_number() over(order by object_id) rk
5 FROM test_top a) t
6 WHERE rk <= 5
7 /
Sous 9i :
SQL> select * from table(dbms_xplan.display)
2 /
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11974 | 2221K| 254 |
|* 1 | VIEW | | 11974 | 2221K| 254 |
| 2 | WINDOW NOSORT | | 11974 | 970K| 254 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 11974 | 970K| 254 |
| 4 | INDEX FULL SCAN | TEST_TOP_II | 11974 | | 54 |
------------------------------------------------------------------------------
Snif, ça ne marche pas... alors que sous 10g :
SQL> set autot on
SQL> /
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 2389K| 231 (1)|
|* 1 | VIEW | | 12879 | 2389K| 231 (1)|
|* 2 | WINDOW NOSORT STOPKEY | | 12879 | 1081K| 231 (1)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231 (1)|
| 4 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30 (4)|
----------------------------------------------------------------------------------
Statistiques
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Le NOSORT STOPKEY ! (et on vérifie sur les consistent gets que ce n'est pas que de la poudre aux yeux :))
Remarquez ici la valeur de la colonne "Rows" du plan d'exécution : ça sera l'objet du point 2 !
1)c.
Ce point n'est pas vraiment lié au type de requête ni aux fonctions analytiques... mais juste à la clause ORDER BY de manière générale. Mais je le cite ici parce que je me suis fait piéger :)
Trier sur une colonne alphanumérique, c'est piégeux. Pourquoi ?
Ben parce que l'ordre des lettres (avec la casse, les caractères bizarres, ...) n'est pas aussi unanime que l'ordre des nombres !
Et du coup, l'ordre réel dans l'index...
Allez, on se refait un test avec la méthode rownum.
Pour cela, on va commencer par créer un index sur object_name :
SQL> ALTER TABLE test_top MODIFY object_name NOT NULL
2 /
Table modifiée.
SQL> CREATE INDEX test_top_iia ON test_top(object_name) COMPUTE STATISTICS
2 /
Index créé.
La requête TOP / ROWNUM donne :
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM test_top
5 ORDER BY object_name)
6 WHERE rownum <= 5
7 /
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | | 313 (2)|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 12879 | 2226K| | 313 (2)|
|* 3 | SORT ORDER BY STOPKEY| | 12879 | 1081K| 2952K| 313 (2)|
| 4 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51 (2)|
---------------------------------------------------------------------------------
Ben ouais, ça chie...
La manière de trier les alphanumériques est définie par le paramètre NLS_SORT. (En fait, c'est un peu plus compliqué puisqu'il y a ausse d'autre NLS_ qui peuvent se mettre des batons dans les roues les uns les autres...)
Mais bien entendu, l'ordonnancement effectif physique des clefs de l'index ne dépend pas de ce paramètre : le tri est effectué en binaire.
Donc pour utiliser l'index, il faut également demander le tri en binaire :
SQL> alter session set nls_sort=binary
2 /
Session modifiée.
Et du coup, ça marche !
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM test_top
5 ORDER BY object_name)
6 WHERE rownum <= 5
7 /
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | 5 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 5 | 885 | 5 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 5 (0)|
| 4 | INDEX FULL SCAN | TEST_TOP_IIA | 5 | | 2 (0)|
-----------------------------------------------------------------------------------
2) Dans la première partie de cette article, j'ai tenté d'attirer votre attention sur une différence notable entre les méthodes ROWNUM et ROW_NUMBER :
La cardinalité !
L'optimiseur estime que la requête renvoie : 5 lignes avec ROWNUM contre... 12879 avec ROW_NUMBER !
Cela pose bien évidemment un problème lorsqu'on réutilise ce morceau de requête.
Un exemple un peu bidon : on fait l'autojointure sur object_id.
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM (SELECT a.*, row_number() over(order by object_id) rk
5 from test_top a) where rk <= 5
6 ) t
7* JOIN test_top b on t.object_id = b.object_id
SQL> /
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 3471K| | 468
|* 1 | HASH JOIN | | 12879 | 3471K| 1240K| 468
| 2 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51
|* 3 | VIEW | | 12879 | 2389K| | 231
|* 4 | WINDOW NOSORT STOPKEY | | 12879 | 1081K| | 231
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| | 231
| 6 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | | 30
------------------------------------------------------------------------------------
Et bien entendu, ça finit par un FULL SCAN alors qu'on devait lire 5 lignes par un index...
D'ailleurs, pour le confirmer, on peut lancer la version ROWNUM :
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1275 | 241
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_TOP | 1 | 86 | 2
| 2 | NESTED LOOPS | | 5 | 1275 | 241
| 3 | VIEW | | 5 | 845 | 231
|* 4 | COUNT STOPKEY | | | |
| 5 | VIEW | | 12879 | 2125K| 231
| 6 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231
| 7 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30
|* 8 | INDEX RANGE SCAN | TEST_TOP_II | 1 | | 1
------------------------------------------------------------------------------
Et là, d'un seul coup, une hypothèse qui provoque la sueur froide : certes le WINDOW STOPKEY permet de limiter le scan... mais le coût associé est celui du scan complet de l'index ??
Petit test :
SQL> select *
2 from test_top
3 order by object_id
4 /
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 1081K| 231
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231
| 2 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30
-------------------------------------------------------------------------
Eh oui, le coût est exactement le même que celui de la requête top...
Pour ce convaincre, de l'absurdité de la chose, on passe le clustering factor de l'index de 200 à 300, et on retente :
SQL> exec dbms_stats.set_index_stats(NULL, 'TEST_TOP_II', clstfct => 300)
Procédure PL/SQL terminée avec succès.
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM (
3 SELECT a.*, row_number() over(order by object_id) rk
4 from test_top a)
5 where rk <= 5
6 /
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 2389K| | 313
|* 1 | VIEW | | 12879 | 2389K| | 313
|* 2 | WINDOW SORT PUSHED RANK| | 12879 | 1081K| 2952K| 313
| 3 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51
---------------------------------------------------------------------------
J'ai essayé de déchiffrer la trace 10053, et comme d'habitude, je nage. J'ai simplement cru comprendre qu'avec ROWNUM l'optimiseur détecte le plan de type "First K rows", et dans le second cas, il considère rk <= 5 comme un filtre sur un resultset non analysé...
Enfin bref l'amère conclusion : il faut probablement préférer ROWNUM pour les top queries simples...
Parmi les méthodes classique, il y a la limitation par ROWNUM bien sûr, ou son pendant "SQL Normé" : les fonctions analytiques.
On va s'intéresser ici à la méthode analytique et des petites difficultés qu'on peut rencontrer :
1) Utilisation de l'index :
a. La contrainte NOT NULL
b. Comportements 9i / 10g
c. Les tris sur colonnes alphanumériques
2) Cardinalité du TOP
Pour commencer, on va se créer un petit jeu de tests basé sur dba_objects :
SQL> CREATE TABLE test_top AS
2 SELECT *
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 /
Table créée.
SQL> CREATE INDEX test_top_ii ON test_top(object_id) COMPUTE STATISTICS
2 /
Index créé.
SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_TOP')
Procédure PL/SQL terminée avec succès.
1) a. Les valeurs NULL ne sont pas stockées dans l'index. Ainsi, lorsqu'on demande toutes les données ordonnées par une colonne définie en NULL, l'index ne peut être utilisé : (Illustration avec la méthode ROWNUM)
SQL> set autotrace traceonly explain
SQL> SELECT * FROM
2 (SELECT *
3 FROM test_top
4 ORDER BY object_id)
5 WHERE rownum <= 5
6 /
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | | 313 (2)|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 12879 | 2226K| | 313 (2)|
|* 3 | SORT ORDER BY STOPKEY| | 12879 | 1081K| 2952K| 313 (2)|
| 4 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51 (2)|
---------------------------------------------------------------------------------
Donc le premier pré-requis pour l'utilisation de l'index : avoir au moins une colonne NOT NULL !
SQL> ALTER TABLE test_top MODIFY object_id NOT NULL;
Table modifiée.
SQL> SELECT * FROM
2 (SELECT *
3 FROM test_top
4 ORDER BY object_id)
5 WHERE rownum <= 5
6 /
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | 3 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 5 | 885 | 3 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 3 (0)|
| 4 | INDEX FULL SCAN | TEST_TOP_II | 5 | | 2 (0)|
----------------------------------------------------------------------------------
On note au passage que le comportement recherché est bien INDEX FULL SCAN + STOPKEY : on lit l'index en commençant par la première feuille, et on limite la recherche. Pour une recherche de borne sur une colonne indexée non null, on aura de la même manière INDEX FULL SCAN (MIN/MAX)...
Autre remarque : pour contourner cette histoire de contrainte NOT NULL, j'ai tenté le ORDER BY NULL LAST... en vain (ce qui se comprend un peu, car même si les NULL sont en derniers, on ne peut affirmer avant exécution qu'ils ne sont pas dans le périmètre de la requête !)
1)b.
A présent, le vif du sujet, qui constitua pour moi une des surprises agréables de la 10g.
Voyons le résultat du la version analytique :
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM
4 (SELECT a.*, row_number() over(order by object_id) rk
5 FROM test_top a) t
6 WHERE rk <= 5
7 /
Sous 9i :
SQL> select * from table(dbms_xplan.display)
2 /
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11974 | 2221K| 254 |
|* 1 | VIEW | | 11974 | 2221K| 254 |
| 2 | WINDOW NOSORT | | 11974 | 970K| 254 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 11974 | 970K| 254 |
| 4 | INDEX FULL SCAN | TEST_TOP_II | 11974 | | 54 |
------------------------------------------------------------------------------
Snif, ça ne marche pas... alors que sous 10g :
SQL> set autot on
SQL> /
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 2389K| 231 (1)|
|* 1 | VIEW | | 12879 | 2389K| 231 (1)|
|* 2 | WINDOW NOSORT STOPKEY | | 12879 | 1081K| 231 (1)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231 (1)|
| 4 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30 (4)|
----------------------------------------------------------------------------------
Statistiques
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Le NOSORT STOPKEY ! (et on vérifie sur les consistent gets que ce n'est pas que de la poudre aux yeux :))
Remarquez ici la valeur de la colonne "Rows" du plan d'exécution : ça sera l'objet du point 2 !
1)c.
Ce point n'est pas vraiment lié au type de requête ni aux fonctions analytiques... mais juste à la clause ORDER BY de manière générale. Mais je le cite ici parce que je me suis fait piéger :)
Trier sur une colonne alphanumérique, c'est piégeux. Pourquoi ?
Ben parce que l'ordre des lettres (avec la casse, les caractères bizarres, ...) n'est pas aussi unanime que l'ordre des nombres !
Et du coup, l'ordre réel dans l'index...
Allez, on se refait un test avec la méthode rownum.
Pour cela, on va commencer par créer un index sur object_name :
SQL> ALTER TABLE test_top MODIFY object_name NOT NULL
2 /
Table modifiée.
SQL> CREATE INDEX test_top_iia ON test_top(object_name) COMPUTE STATISTICS
2 /
Index créé.
La requête TOP / ROWNUM donne :
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM test_top
5 ORDER BY object_name)
6 WHERE rownum <= 5
7 /
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | | 313 (2)|
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 12879 | 2226K| | 313 (2)|
|* 3 | SORT ORDER BY STOPKEY| | 12879 | 1081K| 2952K| 313 (2)|
| 4 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51 (2)|
---------------------------------------------------------------------------------
Ben ouais, ça chie...
La manière de trier les alphanumériques est définie par le paramètre NLS_SORT. (En fait, c'est un peu plus compliqué puisqu'il y a ausse d'autre NLS_ qui peuvent se mettre des batons dans les roues les uns les autres...)
Mais bien entendu, l'ordonnancement effectif physique des clefs de l'index ne dépend pas de ce paramètre : le tri est effectué en binaire.
Donc pour utiliser l'index, il faut également demander le tri en binaire :
SQL> alter session set nls_sort=binary
2 /
Session modifiée.
Et du coup, ça marche !
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM test_top
5 ORDER BY object_name)
6 WHERE rownum <= 5
7 /
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 885 | 5 (0)|
|* 1 | COUNT STOPKEY | | | | |
| 2 | VIEW | | 5 | 885 | 5 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 5 (0)|
| 4 | INDEX FULL SCAN | TEST_TOP_IIA | 5 | | 2 (0)|
-----------------------------------------------------------------------------------
2) Dans la première partie de cette article, j'ai tenté d'attirer votre attention sur une différence notable entre les méthodes ROWNUM et ROW_NUMBER :
La cardinalité !
L'optimiseur estime que la requête renvoie : 5 lignes avec ROWNUM contre... 12879 avec ROW_NUMBER !
Cela pose bien évidemment un problème lorsqu'on réutilise ce morceau de requête.
Un exemple un peu bidon : on fait l'autojointure sur object_id.
SQL> SELECT *
2 FROM
3 (SELECT *
4 FROM (SELECT a.*, row_number() over(order by object_id) rk
5 from test_top a) where rk <= 5
6 ) t
7* JOIN test_top b on t.object_id = b.object_id
SQL> /
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 3471K| | 468
|* 1 | HASH JOIN | | 12879 | 3471K| 1240K| 468
| 2 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51
|* 3 | VIEW | | 12879 | 2389K| | 231
|* 4 | WINDOW NOSORT STOPKEY | | 12879 | 1081K| | 231
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| | 231
| 6 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | | 30
------------------------------------------------------------------------------------
Et bien entendu, ça finit par un FULL SCAN alors qu'on devait lire 5 lignes par un index...
D'ailleurs, pour le confirmer, on peut lancer la version ROWNUM :
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1275 | 241
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_TOP | 1 | 86 | 2
| 2 | NESTED LOOPS | | 5 | 1275 | 241
| 3 | VIEW | | 5 | 845 | 231
|* 4 | COUNT STOPKEY | | | |
| 5 | VIEW | | 12879 | 2125K| 231
| 6 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231
| 7 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30
|* 8 | INDEX RANGE SCAN | TEST_TOP_II | 1 | | 1
------------------------------------------------------------------------------
Et là, d'un seul coup, une hypothèse qui provoque la sueur froide : certes le WINDOW STOPKEY permet de limiter le scan... mais le coût associé est celui du scan complet de l'index ??
Petit test :
SQL> select *
2 from test_top
3 order by object_id
4 /
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 1081K| 231
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TOP | 12879 | 1081K| 231
| 2 | INDEX FULL SCAN | TEST_TOP_II | 12879 | | 30
-------------------------------------------------------------------------
Eh oui, le coût est exactement le même que celui de la requête top...
Pour ce convaincre, de l'absurdité de la chose, on passe le clustering factor de l'index de 200 à 300, et on retente :
SQL> exec dbms_stats.set_index_stats(NULL, 'TEST_TOP_II', clstfct => 300)
Procédure PL/SQL terminée avec succès.
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM (
3 SELECT a.*, row_number() over(order by object_id) rk
4 from test_top a)
5 where rk <= 5
6 /
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12879 | 2389K| | 313
|* 1 | VIEW | | 12879 | 2389K| | 313
|* 2 | WINDOW SORT PUSHED RANK| | 12879 | 1081K| 2952K| 313
| 3 | TABLE ACCESS FULL | TEST_TOP | 12879 | 1081K| | 51
---------------------------------------------------------------------------
J'ai essayé de déchiffrer la trace 10053, et comme d'habitude, je nage. J'ai simplement cru comprendre qu'avec ROWNUM l'optimiseur détecte le plan de type "First K rows", et dans le second cas, il considère rk <= 5 comme un filtre sur un resultset non analysé...
Enfin bref l'amère conclusion : il faut probablement préférer ROWNUM pour les top queries simples...
Partager cet article
