Index non utilisé dans une requête

Version imprimable

Bonjour,
J'ai une requête utilisant une fonction (voir plus bas). Afin d'améliorer les performances, j'ai ajouter un indexe basé sur cette fonction, mais le moteur Oracle ne l'utilise pas, et lorsque je force l'utilisation de cet indexe via un HINT, je vois que le moteur Oracle a raison mais ne comprend pas. Voici le détail :
Voici le 1er plan :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
explain plan for SELECT
  NVL(DOSSIER,'VIDE' ) AS T1
FROM
  MA_TABLE
GROUP BY
   NVL(DOSSIER,'VIDE')
ORDER BY
   T1 ASC;
 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 4111865712
 
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   1 |  SORT ORDER BY      |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   2 |   HASH GROUP BY     |                            |   364 |  2912 | 13002  (10)| 00:02:37 |
|   3 |    TABLE ACCESS FULL| MA_TABLE                   |  2235K|    17M| 12256   (4)| 00:02:28 |
--------------------------------------------------------------------------------------------------

Second plan avec HINT sur l'indexe de fonction :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
explain plan for SELECT /*+ INDEX(MA_TABLE IDX_NVL_MA_TABLE_ASC) */
  NVL(DOSSIER,'VIDE' ) AS T1
FROM
  MA_TABLE
GROUP BY
   NVL(DOSSIER,'VIDE')
ORDER BY
   T1 ASC;
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 1625393906
 
------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   1 |  SORT ORDER BY        |                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   2 |   SORT GROUP BY NOSORT|                        |   364 |  2912 |   373K  (1)| 01:14:48 |
|   3 |    INDEX FULL SCAN    | IDX_NVL_MA_TABLE_ASC   |  2193K|       |  6773   (2)| 00:01:22 |
------------------------------------------------------------------------------------------------

De plus, mon indexe de fonction est déja trié.
Vu que je ne souhaite obtenir que les informations d'un colonne et que cette dernière est indexée, pourquoi Oracle ne l'utilise pas et pourquoi ce plan qui prend 1h12' de plus ?

Ps : taille de la table 472 Mo et 60 416 blocks
taille de l'indexe 56 Mo et 7 168 blocks

Merci d'avance pour votre aide.

05/05/2010, 17h11
Waldar
Qu'est-ce que ça donne avec cette requête :
Code:

1 2 SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1 FROM MA_TABLE;
05/05/2010, 19h40
mnitu

Si vous examinez les deux plans il est facile de voir que la principale différence est donnée par la méthode utilisé pour l'agrégation: SORT GROUP BY (NOSORT) vs HASH GROUP BY.

Bonjour,

Waldar, voici le résultat de la requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1 FROM MA_TABLE; T1 -------------------- TOTO TATA TITI . VIDE . . 967 ligne(s) sÚlectionnÚe(s).
Mnitu :

Citation:

Si vous examinez les deux plans il est facile de voir que la principale différence est donnée par la méthode utilisé pour l'agrégation: SORT GROUP BY (NOSORT) vs HASH GROUP BY.

Oui, ça je l'ai bien vu mais ça ne m'explique pas, ni le choix, ni le chiffrage de l'optimiseur.

D'ailleurs, j'ai relancé la requête avec le HINT en la traçant. Et curieusement, le résultat n'est pas du tout celui prédit par

Code:

EXPLAIN PLAN FOR ...

Voici le résultat de la trace dans TKPROF :
Code:

1 2 3 4 5 6 7 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 66 2.36 5.83 6683 6683 0 967 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 68 2.38 5.84 6683 6683 0 967
Quelqu'un peut t'il m'expliquer pourquoi la prévision de l'optimiseur est à ce point erronnée ?
8O

06/05/2010, 10h00
Waldar

Citation:

Envoyé par tibal

Waldar, voici le résultat de la requête :

Je parlais de l'explain plan et du temps d'exécution bien sûr ;)

Voici Waldar :

Code:

1
2
3
explain plan for 
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 2166119174
 
--------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                        |  2193K|    16M|  1520   (4)| 00:00:19 |
|   1 |  PX COORDINATOR         |                        |       |       |            |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000               |  2193K|    16M|  1520   (4)| 00:00:19 |
|   3 |    PX BLOCK ITERATOR    |                        |  2193K|    16M|  1520   (4)| 00:00:19 |
|   4 |     INDEX FAST FULL SCAN| IDX_NVL_MA_TABLE_ASC   |  2193K|    16M|  1520   (4)| 00:00:19 |
--------------------------------------------------------------------------------------------------

06/05/2010, 10h26
mnitu

Citation:

Envoyé par tibal

...
Mnitu :

Oui, ça je l'ai bien vu mais ça ne m'explique pas, ni le choix, ni le chiffrage de l'optimiseur.

Si vous voulez plus des informations tracer l'optimiseur via l'événement 10053.

Citation:

Envoyé par tibal

...
...
D'ailleurs, j'ai relancé la requête avec le HINT en la traçant. Et curieusement, le résultat n'est pas du tout celui prédit par EXPLAIN PLAN FOR ...

Quelqu'un peut t'il m'expliquer pourquoi la prévision de l'optimiseur est à ce point erronnée ?
8O

Le plan ne dépend pas seulement de la requête mais aussi de pas mal des autres choses: les paramètres de la session, les statistiques, etc.
06/05/2010, 10h49
Waldar

Tibal, ok, mais il faut en tirer les conclusions !
L'explain plan paraît bien, il utilise l'index et il n'y a pas besoin de hint.

La conclusion c'est qu'une requête correctement écrite est meilleure qu'une requête mal écrite.
Utiliser group by pour faire un distinct, c'est très rarement justifiable !
06/05/2010, 11h32
mnitu
Il ne faut pas non plus tirer trop vite des conclusions (mais, je suis tout à fait d'accord Waldar avec ta remarque). Que est-ce que ça donne
Code:

1 2 3 4 5 EXPLAIN plan FOR SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1 FROM MA_TABLE ORDER BY T1

Waldar :

Citation:

Tibal, ok, mais il faut en tirer les conclusions !
L'explain plan paraît bien, il utilise l'index et il n'y a pas besoin de hint.

Oui, mais pas dans la requête initiale, et c'est celle-ci qui est importante.

Citation:

La conclusion c'est qu'une requête correctement écrite est meilleure qu'une requête mal écrite.

Je n'ai pas la main, c'est un applicatif client qui en est à l'origine.

Citation:

Utiliser group by pour faire un distinct, c'est très rarement justifiable !

Il n'y a pas de group by et un distinct dans ma requête initiale.

Mnitu, voici le résulta demandé :

Code:

1
2
3
4
EXPLAIN plan FOR 
SELECT DISTINCT NVL(DOSSIER,'VIDE' ) AS T1
  FROM MA_TABLE
ORDER BY T1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 3763606681
 
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                        |  2193K|    16M|       |  9797   (5)| 00:01:58 |
|   1 |  PX COORDINATOR            |                        |       |       |       |            |       |
|   2 |   PX SEND QC (ORDER)       | :TQ10001               |  2193K|    16M|       |  9797   (5)| 00:01:58 |
|   3 |    SORT ORDER BY           |                        |  2193K|    16M|    67M|  9797   (5)| 00:01:58 |
|   4 |     PX RECEIVE             |                        |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   5 |      PX SEND RANGE         | :TQ10000               |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   6 |       PX BLOCK ITERATOR    |                        |  2193K|    16M|       |  1520   (4)| 00:00:19 |
|   7 |        INDEX FAST FULL SCAN| IDX_T_DOSSIER_USER_ASC |  2193K|    16M|       |  1520   (4)| 00:00:19 |
-------------------------------------------------------------------------------------------------------------

Merci encore à tous les deux.