Oracle n'utilise pas mes index

Version imprimable

Bonsoir à tous,

Je suis confronté à un problème d'optimisation de requête SQL, celle-ci prenant beaucoup trop de temps à l’exécution. Voici la requête :
Code:

1 2 3 4 5 SELECT DCO_NUMCSS, DCO_CODPRV, DCO_NUMFAC, DCO_ENVOI, DCO_DATENV, DCO_PRILIV, DCO_CODECE, DCO_IMMAT, DCO_DATPRL, DCO_DATIMT, DCO_NUMSER FROM DCONTA WHERE DCO_NUMCSS||DCO_NUMSER||DCO_NUMFAC NOT IN ( SELECT CSO_NUMCSS||CSO_NUMSER||CSO_NUMFAC FROM CSSOR );
Les deux tables incriminées, CSSOR et DCONTA sont indexées sur les trois colonnes que je requête dans la clause WHERE (un index de créé sur chacune des deux tables).

Lorsque je demande à ORacle de me fournir le plan d'execution de la requete (Explain Plan), celui-ci ne retourne deux TABLE ACCESS FULL. Je souhaiterais qu'il utilise mes index car je pense que le traitements de la requête irait largement plus vite...? Je ne suis pas doué en tuning / optimisation de requête, donc merci d'avance pour votre indulgence. J'ai pourtant lu par mal de cours mais j'ai surement dû passer à coté de quelques chose, et je ne comprends pas pourquoi.

Est-ce à cause de la concaténation ? Pourtant lorsque je ne prend qu'un seul champ, lui aussi indexé (j'ai testé la création d'index simples et supprimé ceux sur trois colonnes pour le tests) et le résultat est le même, Oracle n'utilise pas l'index. A savoir que j'ai également lancé la commande

"analyze table CSSOR compute statistics;"

Afin qu'Oracle puisse revoir le plan d’exécution.
Merci d'avance pour vos réponses.
Cordialement,

Paul

Et as-tu essayé avec EXISTS ?

Code:

1
2
3
4
5
6
7
8
9
10
SELECT  DCO_NUMCSS, DCO_CODPRV, DCO_NUMFAC, DCO_ENVOI, DCO_DATENV, DCO_PRILIV, DCO_CODECE, DCO_IMMAT, DCO_DATPRL, DCO_DATIMT, DCO_NUMSER 
FROM    DCONTA  DCO
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    CSSOR   CSO 
            WHERE   DCO.DCO_NUMCSS  = CSO.CSO_NUMCSS
                AND DCO.DCO_NUMSER  = CSO.CSO_NUMSER
                AND DCO.DCO_NUMFAC  = CSO.CSO_NUMFAC 
        )
;

[EDIT] C'est bien sur NOT EXISTS !

Bonjour,

1. La concaténation n'est pas une bonne idée. En plus elle peut renvoyer un résultat faux. Pourquoi nhe pas écrire:
Code:

1 2 3 4 5 SELECT DCO_NUMCSS, DCO_CODPRV, DCO_NUMFAC, DCO_ENVOI, DCO_DATENV, DCO_PRILIV, DCO_CODECE, DCO_IMMAT, DCO_DATPRL, DCO_DATIMT, DCO_NUMSER FROM DCONTA WHERE (DCO_NUMCSS,DCO_NUMSER,DCO_NUMFAC) NOT IN ( SELECT CSO_NUMCSS,CSO_NUMSER,CSO_NUMFAC FROM CSSOR );
2. L'utilisation de l'index (et le résultat aussi) peut dépendre du fait que (DCO_NUMCSS,DCO_NUMSER,DCO_NUMFAC) sont nullable ou non.

3. un accés par index n'est pas toujours le plus optimal

Cordialement,
Franck.

Cordialement,
Franck.

17/08/2011, 10h10
piloupolo

Merci

Merci Alain,

Effectivement, avec EXISTS, la requête utilise l'index. Le temps de traitement passe donc de plus de 20 minutes à peine 4 minutes.

Je pense que la clause NOT IN devait empêcher l'utilisation de l'index ?
Merci
Cordialement,

Paul

Citation:

Envoyé par piloupolo

Merci Alain,

Effectivement, avec EXISTS, la requête utilise l'index. Le temps de traitement passe donc de plus de 20 minutes à peine 4 minutes.

Je pense que la clause NOT IN devait empêcher l'utilisation de l'index ?
Merci
Cordialement,

Paul

Lorsque confronté à ce genre de problèmes de non utilisation d’index, il faut revenir à la base. Ici, dans le cas présent, la question qu’il faut se poser est ''comment le CBO interprète t-il l’operateur NOT IN?'' Et bien tout simplement le CBO transforme le NOT IN en <> (différent de).

Code:

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
 
mhouri.world> select owner, object_name
  2  from t
  3  where status not in ('VALID','INVALID');
 
no rows selected
 
mhouri.world> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
SQL_ID  f2gguvfc6nnwy, child number 0                                           
-------------------------------------                                           
select owner, object_name from t where status not in ('VALID','INVALID')        
 
Plan hash value: 2153619298                                                     
 
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |       |       |   136 (100)|          |      
|*  1 |  TABLE ACCESS FULL| T    |     6 |   234 |   136   (2)| 00:00:01 |      
--------------------------------------------------------------------------      
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   1 - filter(("STATUS"<>'VALID' AND "STATUS"<>'INVALID'))                      
 
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement

Sachant que le NOT IN est transformé en <>, et sachant que l’opérateur différent (<>) ne peux pas être couvert par un simple index B-tree, vous avez donc votre réponse concernant la non utilisation de votre index.

Code:

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
 
mhouri.world > create table t as select * from all_objects;
Table created.
 
 
mhouri.world > SELECT /*+ gather_plan_statistics */
  2         t.*
  3    FROM t
  4   WHERE status != 'VALID'
  5  ;
 
 
mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT                                                                                                                                     
--------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=                                                                                
'VALID'                                                                                                                                                                                        
Plan hash value: 2153619298
------------------------------------------------------------------------------------                                                                  
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                  
------------------------------------------------------------------------------------                                                                  
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |                                                                  
------------------------------------------------------------------------------------                                                                  
 
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
   1 - filter("STATUS"<>'VALID')                                                                                                                      
 
Note                                                                                                                                                  
-----                                            
   - dynamic sampling used for this statement                                                                                                                                                      
22 rows selected.

Sachant qu’un B-tree index ne m’est d’aucune utilité dans ce cas, je vais plutôt créer un Function Based index sur la colonne STATUS, et répéter le même select

Code:

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
36
37
 
mhouri.world > create index ind_fbi_status on t (case when status = 'VALID' then NULL else 'X' end );
Index created.
mhouri.world > SELECT /*+ gather_plan_statistics */
				2     t.*
				3  FROM t
				4  WHERE status != 'VALID';
 
30 rows selected.
 
mhouri.world > select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------
SQL_ID  5xtutwubr5xqv, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
SELECT /*+ gather_plan_statistics */        t.* FROM t WHERE status !=                                                                                
'VALID'                                                                                                                                               
 
Plan hash value: 2153619298                                                                                                                           
 
-----------------------------------------------------------------------------                                                                
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                  
------------------------------------------------------------------------------------                                                                  
|*  1 |  TABLE ACCESS FULL| T    |      1 |      6 |     30 |00:00:00.01 |     551 |                                                                  
------------------------------------------------------------------------------------                                                                  
 
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
 
   1 - filter("STATUS"<>'VALID')                                                                                                                      
 
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic sampling used for this statement                                                                                                         
 
22 rows selected.

Oups!!! Malgré la création de ce nouvel index, ce dernier n’a pas été utilisé par le CBO. Le CBO préfère encore faire un FULL TABLE SCAN. Savez vous pourquoi? Tout simplement pour que le CBO puisse utiliser le function based (FBI) index, il faut que la clause where de ma requête et la définition de mon FBI coïncident parfaitement. C’est pourquoi je vais réécrire ma requête comme suit :

Code:

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
36
37
38
39
40
41
 
mhouri.world > SELECT /*+ gather_plan_statistics */
  2         t.*
  3    FROM t
  4    WHERE CASE
  5            WHEN status = 'VALID'
  6               THEN NULL
  7            ELSE 'X'
  8         END = 'X';
 
 
30 rows selected.
 
 mhouri.world >select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                                                     
--------------------------------------------------------------------------------------------------------
SQL_ID  32fcmm91ywcqf, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
SELECT /*+ gather_plan_statistics */        t.*   FROM t  WHERE CASE           WHEN status =                                                          
'VALID'              THEN NULL           ELSE 'X'        END = 'X'                                                                                    
 
Plan hash value: 3817300654                                                                                                                           
 
--------------------------------------------------------------------------------------------------------                                              
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                              
--------------------------------------------------------------------------------------------------------                                              
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |      1 |      6 |     30 |00:00:00.01 |      14 |                                              
|*  2 |   INDEX RANGE SCAN          | IND_FBI_STATUS |      1 |    130 |     30 |00:00:00.01 |       3 |                                              
--------------------------------------------------------------------------------------------------------                                              
 
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
 
   2 - access("T"."SYS_NC00014$"='X')                                                                                                                 
 
Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic sampling used for this statement                                                                                                                                                                                                                                                             
 
23 rows selected.

Et voilà enfin, le CBO a fini par utiliser mon index.

Conclusion
Dans votre cas il y a plusieurs points à considérer
(a) Concaténation de colonnes indexées. Comment réagit le CBO dans ce cas ? je ne l’ai pas essayé encore
(b) Dans le cas où vous n’avez utilisé qu’une seule colonne, c’est bien le NOT IN qui empêche l’utilisation de l’index sur cette colonne. Il faudra penser plutôt à la création d’un function based index dans cette situation
(c) La liste de votre NOT IN est constituée d’un select sur une autre table. A votre place j’utiliserai plutôt un not exists comme suit:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
select 
  DCO_NUMCSS,
  DCO_CODPRV,
  DCO_NUMFAC,
  DCO_ENVOI,
  DCO_DATENV,
  DCO_PRILIV,
  DCO_CODECE,
  DCO_IMMAT,
  DCO_DATPRL,
  DCO_DATIMT,
  dco_numser
from dconta a
where 
   not exists (select null
               from cssor b
               where b.dco_numcss = a.dco_numcss
              );

Faites attention :

aux Not In et les Nulls sinon le résultat n’est pas correct.

aux divers cases d’utilisation d’un index

Code:

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
 
SQL> explain plan for
  2  Select empno
  from emp
 where empno <> 7369
  3    4    5  ;
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2855544112
 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    13 |    52 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PK_EMP |    13 |    52 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
   1 - filter("EMPNO"<>7369)
 
13 ligne(s) sélectionnée(s).

Citation:

Envoyé par mnitu

Faites attention :
[list][*]aux Not In et les Nulls sinon le résultat n’est pas correct.[*]aux divers cases d’utilisation d’un index

Tout à fait

Tiré de mon blog

Citation:

Code:

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
36
 
Warning: the above FBI index will not work correctly if the status column is allowed to be null.
 
 mhouri.world> update t  
 2  set status = null 
 
3  where status != 'VALID' 
4  and rownum <= 4;  
 
4 rows updated.  
 
mhouri.world> commit;  
 
Commit complete.  
 
mhouri.world> SELECT count(1)  
2    FROM t  
3   WHERE status != 'VALID';  
 
 COUNT(1)  
----------  
   26  
 
mhouri.world>  SELECT count(1)  
2       FROM t  
3       WHERE CASE 
4               WHEN status = 'VALID' 
5                  THEN NULL 
6               ELSE 'X' 
7            END = 'X';  
 
 COUNT(1)  
 ----------  
   30 
 
!!!

Code:

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
 
mhouri.world> create index ind_fbi_status on t(case when nvl(status,'VALID') = 'VALID' then null else 'X' end);  
 
Index created.  
 
mhouri.world> select count(1)  
2  from t  
3  where case when nvl(status,'VALID') = 'VALID' 
4                 then null 
5             else 'X' 
6             end = 'X';  
 
COUNT(1)  
----------  
26  
 
mhouri.world> select * from table(dbms_xplan.display_cursor);  
 
PLAN_TABLE_OUTPUT  
 
-------------------------------------------------------------------------------------  
 
SQL_ID  ffj2y4c2q8vup, child number 0  
 
 -------------------------------------  
 
 select count(1) from t where case when nvl(status,'VALID') = 'VALID' 
 
 then null            else 'X'            end = 'X' 
 Plan hash value: 3388779105  
 
 ------------------------------------------------------------------------------------  
 
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |  
 
------------------------------------------------------------------------------------  
 
|   0 | SELECT STATEMENT  |                |       |       |     1 (100)|          |  
 
|   1 |  SORT AGGREGATE   |                |     1 |     5 |            |          |  
 
|*  2 |   INDEX RANGE SCAN| IND_FBI_STATUS |     6 |    30 |     1   (0)| 00:00:01 |  
 
------------------------------------------------------------------------------------   
 
Predicate Information (identified by operation id):  
---------------------------------------------------  
2 - access("T"."SYS_NC00014$"='X')  
 
Note  
 
-----  
- dynamic sampling used for this statement

17/08/2011, 13h21
piloupolo

Merci

Merci à tous, super les explications, j'ai parfaitement compris :) Tout cela ira mieux d'ici quelques semaines, lorsque j'aurai assisté à ma formation Oracle PL/SQL :)
Un grand merci à vous tous pour l'exactitude et la rapidité de vos réponses.

Marius,

Pour en revenir au cas que vous avez posté

Code:

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
 
SQL> EXPLAIN plan FOR
  2  SELECT empno
  FROM emp
 WHERE empno <> 7369
  ;
 
Explicité.
 
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 2855544112 
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |    13 |    52 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | PK_EMP |    13 |    52 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
    1 - filter("EMPNO"<>7369)

Ceci ne veut pas dire que notre index PK_EMP a bien couvert notre where clause where empno <> 7369. Mais ceci veut dire que le CBO, voyant que la seule colonne à sélectionner existe au sein de l'index PK_EMP, il a estimé que l'accès à l'index dans ce cas est moins couteux qu'un accès à la table. Il accède alors à cet index (non pas pour trouver un rowid vers la table, mais bien pour trouver l'enregistrement recherché) en faisant par la suite un filtre (empno <> 7369) afin de jeter ce qui ne convient pas et garder le reste.

Si par exemple j'ajoute une seule colonne à la requête, l'index n'est plus, ni accédé, ni utilisé

Code:

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
 
mhouri.world> explain plan for
  2  select empno, ename
  3  from emp
  4  where empno <> 7369;
 
Explained.
 
mhouri.world> select * from table (dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 2872589290                                                     
 
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |    14 |   140 |     5   (0)| 00:00:01 |      
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   140 |     5   (0)| 00:00:01 |      
--------------------------------------------------------------------------      
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                                                                                                            
   1 - filter("EMPNO"<>7369)                                                    
 
13 rows selected.

Par contre si j'enrichi mon index avec la colonne ename, l'index sera de nouveau désiré par le CBO

Code:

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
 
 
mhouri.world > create index emp_enm on emp (empno, ename);
 
Index created.
 
mhouri.world > explain plan for
  2  select empno
  3       ,ename
  4  from emp
  5  where empno <> 7369;
 
Explained.
 
mhouri.world > select * from table (dbms_xplan.display);
 
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 1499357153                                                     
 
----------------------------------------------------------------------------    
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |    
----------------------------------------------------------------------------    
|   0 | SELECT STATEMENT |         |    14 |   140 |     1   (0)| 00:00:01 |    
|*  1 |  INDEX FULL SCAN | EMP_ENM |    14 |   140 |     1   (0)| 00:00:01 |    
----------------------------------------------------------------------------    
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                                                                                                            
   1 - filter("EMPNO"<>7369)                                                    
 
13 rows selected.

C'est pourquoi il est parfois très judicieux de créér un index qui "match" les colonnes sélectionées afin d'eviter l'accès à la table

17/08/2011, 17h02
mnitu

Mohamed,
C’était juste pour nuancer vos propos

Citation:

« …et sachant que l’opérateur différent (<>) ne peux pas être couvert par un simple index B-tree »
«Sachant qu’un B-tree index ne m’est d’aucune utilité dans ce cas …»

qui ont sonnées pour moi comme des autres "fausses" vérités : Oracle n’index pas les nulls, utilisez toujours des variables de liaisons dans vos requêtes, etc.

D’autre part je trouve un peu forcé l’idée de créer un index fonctionnel dans ce cas.
17/08/2011, 17h12
Mohamed.Houri

Citation:

Envoyé par mnitu

Mohamed,
C’était juste pour nuancer vos propos

qui ont sonnées pour moi comme des autres "fausses" vérités : Oracle n’index pas les nulls, utilisez toujours des variables de liaisons dans vos requêtes, etc.

D’autre part je trouve un peu forcé l’idée de créer un index fonctionnel dans ce cas.

Ok marius, merci. Je préfère les réponses directes. Cela me permettra d'avancer plus vite :)
17/08/2011, 19h42
pachot

Bonjour,

Citation:

Envoyé par piloupolo

Je pense que la clause NOT IN devait empêcher l'utilisation de l'index ?

Les requêtes avec NOT IN ne ramènent pas le même résultat. Ils ne sont équivalentes que si la sous requête ne renvoie jamais de NULL. Et lorsque elle ne ramène jamais de null (parce que les colonnes sont not null ou qu'on rajoute des prédicats is not null) l'index est utilisé dans les 2 cas.

Le problème n'est pas d'écrire d'une façon ou d'une autre. L'optimiseur va de toute façon le réécrire à sa sauce. Mais d'être précis sur les données et le résultat qu'on veut.

Cordialement,
Franck.
18/08/2011, 12h31
Mohamed.Houri

Pour plus d'information à ce sujet

http://richardfoote.wordpress.com/20...-not-now-john/

http://jonathanlewis.wordpress.com/2007/02/25/not-in/
18/08/2011, 14h30
mnitu

Citation:

Envoyé par Mohamed.Houri

Pour plus d'information à ce sujet

http://richardfoote.wordpress.com/20...-not-now-john/
...

Citation:

Therefore the CBO simply ignores indexes when costing a NOT EQUAL condition. Why bother going to all the overhead of calculating the cost of using an index to retrieve the vast majority of rows when a Full Table Scan is going to be the cheaper alternative in the vast majority of such cases. So the CBO doesn’t even bother trying and ignores all indexes that could potentially be used to retrieve the rows based on the NOT EQUAL condition.

Comme vous l'avez déjà constaté il se trompe royalement.
18/08/2011, 14h33
mnitu

Citation:

Envoyé par Mohamed.Houri

Pour plus d'information à ce sujet
http://jonathanlewis.wordpress.com/2007/02/25/not-in/

Utile mais, je ne vois rien de nouveau par rapport à notre discussion.