Optimisation : ramener n lignes sur une table volumineuse

**Casshern** · 24/05/2012, 18h22

Bonjour,
j'ai la table suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE T1(ID1 VARCHAR2(20),ID2 NUMBER,ID3 VARCHAR2(10),ID4 NUMBER,ID5 VARCHAR2(20),COL1 VARCHAR2(5)NOT NULL,COL2 VARCHAR2(5) ,COL3 VARCHAR2(5),COL4 VARCHAR2(5),COL5 VARCHAR2(5),PRIMARY KEY(ID1,ID2,ID3,ID4,ID5));
CREATE INDEX T1_idx ON T1(ID1, ID2, COL1); 
SELECT ID1,ID2,COL1,COUNT(*)FROM T1 GROUP BY ID1,ID2,COL1;
ID1 ID2 COL1 COUNT(*)
-------------- ------------ ------- --------
VALEUR1           20 D         70 
VALEUR1           23 D         70 
VALEUR1            6 D    1706000 
VALEUR1           14 D         70 
VALEUR1           15 D         70 
VALEUR1           16 D         70 
VALEUR1           18 D         70 
VALEUR1           26 D         70 
VALEUR1           17 D         70 
VALEUR1           25 D         70 
VALEUR1           13 D         70 
VALEUR1           24 D         70 
VALEUR1           28 D         70 
VALEUR1          100 D    1706500 
VALEUR1          1 D         71 
VALEUR1          1 true           5 
VALEUR1           10 D         70 
VALEUR1           21 D         70 
VALEUR1           29 D         70 
VALEUR1          101 D    1706500 
VALEUR1            5 D    1706000 
VALEUR1            9 D         70 
VALEUR1           11 D         35 
VALEUR1           19 D         70 
VALEUR1           22 D         70 
VALEUR1           27 D         70

et la requête suivante à optimiser (N un nombre quelconque)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT*FROM(SELECT T1.ID1,T1.ID3,T1.ID4,Rank()over(ORDER BY T1.ID3,T1.ID4,T1.ID5)MON_ORDRE FROM T1 WHERE T1.ID1='VALEUR1'AND T1.ID2=1 AND T1.COL1='D')WHERE MON_ORDRE BETWEEN N AND N+10;
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   821K|    31M|       | 15195   (1)|202:48:58 |
|*  1 |  VIEW                 |              |   821K|    31M|       | 15195   (1)|202:48:58 |
|   2 |   COUNT               |              |       |       |       |            |          |
|   3 |    VIEW               |              |   821K|    21M|       | 15195   (1)|202:48:58 |
|   4 |     SORT ORDER BY     |              |   821K|    33M|    47M| 15195   (1)|202:48:58 |
|*  5 |      TABLE ACCESS FULL| T1           |   821K|    33M|       |  7194   (0)| 96:01:21 |
----------------------------------------------------------------------------------------------

Pourriez-vous me dire si il est possible d'optimiser cette instruction SELECT?
Pour information ,la requête suivante donne le même coût.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT * FROM (SELECT T1.ID1,T1.ID3,T1.ID4,Rank()over(ORDER BY T1.ID3,T1.ID4,T1.ID5)MON_ORDRE FROM T1 WHERE T1.ID1='VALEUR1' AND T1.ID2=1 AND T1.EST_VUE='D') WHERE MON_ORDRE BETWEEN N AND N+10;
 
-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |              |   821K|    31M|       | 15195   (1)|202:48:58 |
|*  1 |  VIEW                    |              |   821K|    31M|       | 15195   (1)|202:48:58 |
|*  2 |   WINDOW SORT PUSHED RANK|              |   821K|    33M|    47M| 15195   (1)|202:48:58 |
|*  3 |    TABLE ACCESS FULL     | T1           |   821K|    33M|       |  7194   (0)| 96:01:21 |
-------------------------------------------------------------------------------------------------

**al1_24** · 24/05/2012, 18h44

As-tu des index sur cette table ?
Un index sur (COL1, ID1, ID2, ID3, ID4, ID5) qui sont utilisées soit pour la restriction soit pour le tri te permettrait sans doute d'éviter le FULL SCAN.

**skuatamad** · 24/05/2012, 19h33

Quelles versions d'Oracle ?
Les statistiques de la table et de l'index sont elles à jour ?

A priori l'optimiseur estime très mal le nombre de lignes (colonne rows) à ramener, mais un plan plus détaillé avec également la partie Filter serait intéressant, pour ça utilise la méthode décrite par Mohamed Houri :
http://www.developpez.net/forums/d11...e/#post6505385

**amir.** · 24/05/2012, 20h20

Salut,

Je suis pas tout à fait d'accord avec al1_24. Un index n'est pas utilisé lors de l'ORDER BY.

Prenosn un exemple pour illustrer mon propos:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE EX_TRI_1
(
   COL_1 VARCHAR2(100),
   COL_2 VARCHAR2(100)
);
table EX_TRI_1 created.
 
 
INSERT INTO EX_TRI_1
SELECT dbms_random.string('A', 100), dbms_random.string('A', 100)
  FROM all_objects
 WHERE rownum <= 50000;
50,000 rows inserted.
 
COMMIT;

Un premier test:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
SELECT col_1
  FROM ex_tri_1
 WHERE lower(col_1) LIKE 'tes%'
 ORDER BY col_1;
 
COL_1
----------------------------------------------------------------------------------------------------
TESHossAzlwuAdKBMMluMjoAwsLXNGXFAuAqAVzZVsVvEFNktxIscHEPweeBzQGAfxkByKbhEHpQoClpqUPqNcVxYfszlGZBpGwD
TEsHtKDfAHOQugHhDuKQrrfzCbvpwYKrrFBiXhItawJCcDiAiNyHSbKWgnwpiNXsNPyROQbdqTSTvOzlptwOzUOXsyXGvfmnLHUq
tESjrrzJlGnXzFqdbFjLQSlmqHZfMiRPdgqLxaytFXllkuHgNNnwNrGkLsPEaRZOmdixfpWhdUwBMsTEUMbaaXAoYFPYrYHsBUiQ
tEStEopZMrzJjRZWReEXNSfSNdKtQigIwxloYetNWaOJvdLmCdoXRTeGhdnVXWiIokyIvTjoFTgIwqEPGzQhCLPdaYJvAcVLKNRq
 
 
select * from table(dbms_xplan.display_cursor(null,null,'BASIC'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT col_1   FROM ex_tri_1  WHERE lower(col_1) LIKE 'tes%'  ORDER BY
col_1
 
Plan hash value: 2350637384
 
---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  SORT ORDER BY     |          |
|   2 |   TABLE ACCESS FULL| EX_TRI_1 |
---------------------------------------
 
 
 15 rows selected

Aucun index présent, aucun index utilisé dans notre premier exemple

Ajoutons un petit (functional) index sur la colonne...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
CREATE INDEX ex_tri_idx_1 ON ex_tri_1 (lower(col_1));
index EX_TRI_IDX_1 created.

On recommence la même requête que précédemment et voici le plan:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT col_1   FROM ex_tri_1  WHERE lower(col_1) LIKE 'tes%'  ORDER BY
col_1
 
Plan hash value: 199684959
 
-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  SORT ORDER BY               |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| EX_TRI_1     |
|   3 |    INDEX RANGE SCAN          | EX_TRI_IDX_1 |
-----------------------------------------------------
 
 
 16 rows selected

Vu que j'utilise COL_1 dans le WHERE, mon index est utilisé et comme ORacle récupère les enregistrements au travers de l'index de manière triée, l'optimizer évite un second tri.

Par contre... Si j'exécute la requête suivante, ou ce n'est pas col_1 qui est utilisé dans le WHERE mais bien dans le tri, j'ai le plan suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT col_2
  FROM ex_tri_1
 WHERE lower(col_2) LIKE 'tes%'
 ORDER BY col_1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
EXPLAINED SQL STATEMENT:
------------------------
SELECT col_2   FROM ex_tri_1  WHERE lower(col_2) LIKE 'tes%'  ORDER BY
col_1
 
Plan hash value: 2350637384
 
---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  SORT ORDER BY     |          |
|   2 |   TABLE ACCESS FULL| EX_TRI_1 |
---------------------------------------
 
 
 15 rows selected

Bien que mon index est encore présent, Oracle ne l'utilise pas pour le tri. Mais si la colonne COL_1 est sélectionnée dans le SELECT et le WHERE se fait sur COL_2, Oracle pourrait utiliser l'index... Ca c'était une petite parenthèse.

Maintenant, la question principale... Faire du paging avec Oracle en utilisant rank()... Je me demandais comment ton paging pourrait fonctionner si tu as des sessions concurentes qui mettent à jour les données? Il se pourrait qu'entre deux exécutions, la requête renvoie un résultat différent. Non? Et pourquoi ne pas utiliser simplement rownum? Pourquoi encore calculé un rank()??

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT*
  FROM (
        SELECT T1.ID1,
		       T1.ID3,
			   T1.ID4,
		       Rank()over(ORDER BY T1.ID3,T1.ID4,T1.ID5) MON_ORDRE 
	      FROM T1 
		 WHERE T1.ID1 = 'VALEUR1'
		   AND T1.ID2=1 
		   AND T1.COL1='D'
	)
 WHERE MON_ORDRE BETWEEN N AND N+10;

Aussi la fonction analytique RANK() peut renvoyer plusieurs fois le même ranking. Donc la requête peut renvoyer plus de 10 enregistrements. Peut-être que row_number() est plus adapté dans ce cas-ci? (quoi que je persiste à penser que rownum pourrait faire l'affaire).

Aussi, que contiennent les champs T1.ID3,T1.ID4,T1.ID5?? Un index pourrait aider aussi sur ces colonnes.

Et effectivement, comme skuatamad les cardinalités pourraient aussi aider à voir que fais Oracle...

**Casshern** · 24/05/2012, 21h26

Je n'ai pas accès aux statistiques

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM v$version WHERE ROWNUM=1;
BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
 
SHOW PARAMETER OPTIMIZIER;
Echec de la requête d'affichage des paramètres 
 
SHOW PARAMETER DB_FILE_MUL%
Echec de la requête d'affichage des paramètres
 
SHOW PARAMETER CURSOR_SHARING
Echec de la requête d'affichage des paramètres

Les colonnes ID1,ID2,ID3,ID4 et ID5 peuvent prendre n'importe quelle valeur.
La colonne COL1 ne peut prendre que 2 valeurs.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
| Id  | Operation             | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
|   0 | SELECT STATEMENT      |              |   821K|    31M|       | 15195   (1)|202:48:58 |                                                                                                                                                                                                               
|*  1 |  VIEW                 |              |   821K|    31M|       | 15195   (1)|202:48:58 |                                                                                                                                                                                                               
|   2 |   COUNT               |              |       |       |       |            |          |                                                                                                                                                                                                               
|   3 |    VIEW               |              |   821K|    21M|       | 15195   (1)|202:48:58 |                                                                                                                                                                                                               
|   4 |     SORT ORDER BY     |              |   821K|    33M|    47M| 15195   (1)|202:48:58 |                                                                                                                                                                                                               
|*  5 |      TABLE ACCESS FULL| T1           |   821K|    33M|       |  7194   (0)| 96:01:21 |                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------                                                                                                                                                                                                               
 
1 - filter("X"."RN"<=10 AND "X"."RN">0)                                                                                                                                                                                                                                                                      
5 - filter("ID2"=1 AND "ID1"='VALEUR1' AND                                                                                                                                                                                                                                      
"COL1"='D')

J'ai crée un index sur les colonnes apparaissant dans le SELECT,WHERE et ORDER et on obtient une légère amélioration.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE INDEX T1_idx2 ON T1(ID1,ID2,ID3,ID4,ID5,COL1);
 
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |   821K|    31M|       | 14245   (1)|190:08:08 |
|*  1 |  VIEW                |                   |   821K|    31M|       | 14245   (1)|190:08:08 |
|   2 |   COUNT              |                   |       |       |       |            |          |
|   3 |    VIEW              |                   |   821K|    21M|       | 14245   (1)|190:08:08 |
|   4 |     SORT ORDER BY    |                   |   821K|    33M|    47M| 14245   (1)|190:08:08 |
|*  5 |      INDEX RANGE SCAN| T1_IDX2           |   821K|    33M|       |  6244   (0)| 83:20:31 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("X"."RN"<=10 AND "X"."RN">0)
   5 - access("ID1"='VALEUR1' AND "ID2"=1 AND 
              "COL1"='D')
       filter("COL1"='D')

**amir.** · 24/05/2012, 21h41

Légère amélioration car Oracle utilise ton index pour récupérer les enregistrements directement dans le bon ordre. Ca évite une opération de tri (comme j'expliquais avant).

Mais, je comprend pas quand tu dis ne pas avoir accès aux statistiques. Est-ce que par exemple tu vois le contenu de la table user_tables (genre num_rows ou avg_row_len...) ou user_tab_columns (num_distcint, low_value, density...)?

Un petit conseil, évide de mettre en première colonne d'un index une colonne qui n'a pas une grande densité de valeur. A ta place, je n'indexerai même pas cette colonne.

**Mohamed.Houri** · 25/05/2012, 10h04

Envoyé par amir.

Salut,

Je suis pas tout à fait d'accord avec al1_24. Un index n'est pas utilisé lors de l'ORDER BY.

Vu que j'utilise COL_1 dans le WHERE, mon index est utilisé et comme ORacle récupère les enregistrements au travers de l'index de manière triée, l'optimizer évite un second tri.

Donc, ceux qui vous lisent, comprennent que grâce à votre index Oracle a évité un second tri!!!

Quel second tri? y-avait-il deux ORDER BY dans votre requête? bien sûr que non.

Dans votre exemple, Oracle a bien utilisé l'index pour accéder à la table mais n'a pas pu s'empecher de faire une Opération supplémentaire "SORT ORDER BY" pour trier les données comme demandé.

Par contre, lorsque je change légèrement l'ORDER BY pour qu'il coincide parfaitement avec votre index, j'obtiens ce qui suit:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
mhouri > select
  2    col_1
  3  from ex_tri_1
  4  where lower(col_1) like 'tes%'
  5  ORDER BY lower(col_1);
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |   267 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EX_TRI_1     |    12 |   624 |   267   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | EX_TRI_IDX_1 |   388 |       |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EX_TRI_1"."SYS_NC00003$" LIKE 'tes%')
       filter("EX_TRI_1"."SYS_NC00003$" LIKE 'tes%')
 
Note
-----
   - dynamic sampling used for this statement

Où vous pouvez remarquer l'absence de toute opération "ORDER BY". Ceci est la preuve que non seulement l'index a été utilisé pour accéder à la table mais également pour éviter toute autre opération ORDER BY.

Pour rappel, lorsque vous avez un ORDER BY dans votre requête et que le plan d'exécution ne montre

aucune opération ORDER BY
ou une opération SORT (ORDER BY) NO SORT

Ceci est une indication qu'Oracle a pu utiliser un index afin d'accomplir l'opération de tri demandée

**mnitu** · 25/05/2012, 11h35

Deux questions :
Ya t-il des histogrammes sur la colonne id2 la distribution des valeurs est non-uniforme
Vos requêtes utilisent des variables de liaison ou des littéraux codées en dur.

**Casshern** · 26/05/2012, 13h28

Pas de variables de liaison (littéraux codés en dur).
Je suis parvenu à optimiser la requête en recréant la table T1 en tant que table organisée en index.
Merci pour votre aide.

**PpPool** · 30/05/2012, 17h39

Bien que résolu, va jeter un coup d'oeil sur ce topic de notre ami Tom : http://www.oracle.com/technetwork/is...om-086197.html

Optimisation : ramener n lignes sur une table volumineuse

SQL Oracle

Discussions similaires

Partager

Partager