Utilisation d'un index

**Snipah** · 26/03/2009, 17h13

Bonjour,

J'aimerai vous soumettre une question concernant l'utilisation d'index.
Voici la table concernée :

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE REA_NOTIF_EXPORT_BANDE  (
   PK_NOTIF_EXPORT_BANDE NUMBER,
   BANDE VARCHAR2(32),
   DATEHEURE DATE,
   STATUT VARCHAR2(32)
);

Voici un index créé sur cette table :

Code :

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

1
2
3
4
 
CREATE INDEX IDX_EXPBD_HEUREBANDE_FCT ON REA_NOTIF_EXPORT_BANDE
(EXTRAIRE_SAVEDAY(DATEHEURE), BANDE)
;

La fonction extraire_saveday fait un trunc au jour près de la date entrée en paramètre, et la table contient environ 14000 enregistrements.

Si j'execute cette requete, l'index est bien utilisé :

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
 
SQL> select exp.bande, exp.dateheure, exp.statut from
  2  ( 
  3  select 'PC0147L3' as BANDE, to_date('24/03/2009','dd/mm/yyyy') as SAVEDAY FROM DUAL 
  4  ) bd
  5  JOIN rea_notif_export_bande exp
  6   ON extraire_saveday(exp.dateheure) = bd.saveday
  7   AND exp.bande = bd.bande;
 
BANDE                            DATEHEUR STATUT
-------------------------------- -------- --------------------------------
PC0147L3                         25/03/09 1
 
 
Plan d''exécution
----------------------------------------------------------
Plan hash value: 1259708469
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    20 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                          |     1 |    20 |     5   (0)| 00:00:01 |
|   2 |   FAST DUAL                  |                          |     1 |       |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| REA_NOTIF_EXPORT_BANDE   |     1 |    20 |     3   (0)| 00:00:0
|*  4 |    INDEX RANGE SCAN          | IDX_EXPBD_HEUREBANDE_FCT |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Maintenant, si je remplace le "select ... from dual" par une autre requete ne me renvoyant qu'une ligne exactement identique, l'index ne sera plus utilisé.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL> select distinct bande, saveday from rea_service serv
  2     JOIN rea_notif_ntw_savegroup clo
  3        ON clo.fk_service = serv.pk_service
  4       JOIN rea_notif_ntw_saveset ss
  5           ON clo.pk_notif_ntw_savegroup = ss.fk_notif_ntw_savegroup
  6         JOIN rea_notif_ntw_bande bd
  7           ON ss.pk_notif_ntw_saveset = bd.fk_notif_ntw_saveset
  8  WHERE pk_service = 68062;
 
BANDE                            SAVEDAY
-------------------------------- --------
PC0147L3                         24/03/09

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
36
37
38
39
40
41
42
43
44
45
46
 
SQL> select exp.bande, exp.dateheure, exp.statut from
  2  (
  3  select distinct bande, saveday from rea_service serv
  4     JOIN rea_notif_ntw_savegroup clo
  5        ON clo.fk_service = serv.pk_service
  6       JOIN rea_notif_ntw_saveset ss
  7           ON clo.pk_notif_ntw_savegroup = ss.fk_notif_ntw_savegroup
  8         JOIN rea_notif_ntw_bande bd
  9           ON ss.pk_notif_ntw_saveset = bd.fk_notif_ntw_saveset
 10  WHERE pk_service = 68062
 11  ) bd
 12  JOIN rea_notif_export_bande exp
 13          ON extraire_saveday(exp.dateheure) = bd.saveday
 14          AND exp.bande = bd.bande;
 
 
BANDE                            DATEHEUR STATUT
-------------------------------- -------- --------------------------------
PC0147L3                         25/03/09 1            
 
 
Plan d''exécution
----------------------------------------------------------
Plan hash value: 1172785269
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     2 |   350 |   220   (2)| 00:00:03 |
|   1 |  VIEW                              |                              |     2 |   350 |   220   (2)| 00:00:03 |
|   2 |   HASH UNIQUE                      |                              |     2 |   214 |   220   (2)| 00:00:03 |
|*  3 |    HASH JOIN                       |                              |     2 |   214 |   219   (1)| 00:00:03 |
|*  4 |     TABLE ACCESS BY INDEX ROWID    | REA_NOTIF_NTW_BANDE          |     1 |    15 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                              |    38 |  2204 |   162   (0)| 00:00:02 |
|   6 |       NESTED LOOPS                 |                              |    41 |  1763 |    39   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                              |     2 |    66 |     5   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| REA_SERVICE                  |     1 |    18 |     2   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN         | IDX_REA_SERVICE_PK           |     1 |       |     1   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID| REA_NOTIF_NTW_SAVEGROUP      |     2 |    30 |     3   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | IDX_REA_SAVEGROUP_FK_SERVICE |     2 |       |     1   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | REA_NOTIF_NTW_SAVESET        |    25 |   250 |    21   (0)| 
|* 13 |         INDEX RANGE SCAN           | IDX_REA_SAVESET_FK_SAVEGROUP |    25 |       |     2   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN             | IDX_REA_BANDE_FK_SAVESET     |     1 |       |     2   (0)| 00:00:01 |
|  15 |     TABLE ACCESS FULL              | REA_NOTIF_EXPORT_BANDE       | 14126 |   675K|    56   (2)| 00:00:01
----------------------------------------------------------------------------------------------------

Il fait maintenant un ACCESS FULL sur la table REA_NOTIF_EXPORT_BANDE, pourtant la jointure est à première vue identique.
Quelqu'un saurait-il m'expliquer ce phénomène?
Merci d'avance.

EDIT : petit oubli, il s'agit d'Oracle 10g.

**Waldar** · 26/03/2009, 17h32

Le saveday de votre seconde requête est bien une date ?

J'ai du mal à comprendre votre fonction, en quoi diffère-t'elle de "trunc(<madate>)" ?

Sinon vous pouvez probablement forcer l'index en réécrivant la requête :

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
SELECT
    exp.bande, exp.dateheure, exp.statut
FROM
    rea_notif_export_bande exp
WHERE
    (extraire_saveday(exp.dateheure), exp.bande) =
           (SELECT DISTINCT
                saveday, bande
            FROM
                rea_service serv
                INNER JOIN rea_notif_ntw_savegroup clo
                  ON clo.fk_service = serv.pk_service
                INNER JOIN rea_notif_ntw_saveset ss
                  ON ss.fk_notif_ntw_savegroup = clo.pk_notif_ntw_savegroup
                INNER JOIN rea_notif_ntw_bande bd
                  ON bd.fk_notif_ntw_saveset = ss.pk_notif_ntw_saveset
            WHERE
                pk_service = 68062);

Une dernière remarque, vous aliasez vos tables ce qui est très bien, mais quel dommage de ne pas utiliser ces alias dans le select et le where (cf. la sous-requête) !

**Snipah** · 26/03/2009, 18h02

Envoyé par Waldar

Le saveday de votre seconde requête est bien une date ?

J'ai du mal à comprendre votre fonction, en quoi diffère-t'elle de "trunc(<madate>)" ?

Oui, le saveday de la seconde requête est bien une date.
Et la fonction en question est en fait un trunc(parametre-19/24,'dd'), les journées étant comptées de 19h à 19h. Le jour où ça change, j'aurai juste à ajuster la fonction.

J'ai testé votre requête, elle utilise bien l'index :

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
36
37
38
39
40
41
42
43
44
45
46
47
SQL> SELECT
  2      exp.bande, exp.dateheure, exp.statut
  3  FROM
  4      rea_notif_export_bande exp
  5  WHERE
  6      (extraire_saveday(exp.dateheure), exp.bande) =
  7             (SELECT DISTINCT
  8                  saveday, bande
  9              FROM
 10                  rea_service serv
 11                  INNER JOIN rea_notif_ntw_savegroup clo
 12                    ON clo.fk_service = serv.pk_service
 13                  INNER JOIN rea_notif_ntw_saveset ss
 14                    ON ss.fk_notif_ntw_savegroup = clo.pk_notif_ntw_savegroup
 15                  INNER JOIN rea_notif_ntw_bande bd
 16                    ON bd.fk_notif_ntw_saveset = ss.pk_notif_ntw_saveset
 17              WHERE
 18                  pk_service = 68062);
 
BANDE                            DATEHEUR STATUT
-------------------------------- -------- --------------------------------
PC0147L3                         25/03/09 1
 
 
Plan d''exécution
----------------------------------------------------------
Plan hash value: 3981013505
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     1 |    20 |   330   (1)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID       | REA_NOTIF_EXPORT_BANDE       |     1 |    20 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                 | IDX_EXPBD_HEUREBANDE_FCT     |     2 |       |     1   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                     |                              |    41 |  1968 |   163   (1)| 00:00:02 |
|   4 |     TABLE ACCESS BY INDEX ROWID    | REA_NOTIF_NTW_BANDE          |     1 |    15 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                              |    41 |  1968 |   162   (0)| 00:00:02 |
|   6 |       NESTED LOOPS                 |                              |    41 |  1353 |    39   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                              |     2 |    46 |     5   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| REA_SERVICE                  |     1 |    13 |     2   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN         | IDX_REA_SERVICE_PK           |     1 |       |     1   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID| REA_NOTIF_NTW_SAVEGROUP      |     2 |    20 |     3   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | IDX_REA_SAVEGROUP_FK_SERVICE |     2 |       |     1   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | REA_NOTIF_NTW_SAVESET        |    25 |   250 |    21   (0)| 
|* 13 |         INDEX RANGE SCAN           | IDX_REA_SAVESET_FK_SAVEGROUP |    25 |       |     2   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN             | IDX_REA_BANDE_FK_SAVESET     |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Le cout est par contre supérieure à la précédente requête qui ne l'utilise pas, ça expliquerait qu'il ne soit pas utilisé dans ce cas?

Un autre 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
36
37
38
39
40
41
42
43
44
SQL> select exp.bande, exp.dateheure from
  2  ( 
  3  select distinct bd.bande, serv.saveday from rea_service serv
  4     JOIN rea_notif_ntw_savegroup clo
  5        ON clo.fk_service = serv.pk_service
  6       JOIN rea_notif_ntw_saveset ss
  7           ON clo.pk_notif_ntw_savegroup = ss.fk_notif_ntw_savegroup
  8         JOIN rea_notif_ntw_bande bd
  9           ON ss.pk_notif_ntw_saveset = bd.fk_notif_ntw_saveset
 10  WHERE serv.pk_service = 68062
 11  ) bd
 12  JOIN rea_notif_export_bande exp
 13   ON extraire_saveday(exp.dateheure) = bd.saveday
 14   AND exp.bande = bd.bande;
 
BANDE                            DATEHEUR
-------------------------------- --------
PC0147L3                         25/03/09
 
 
Plan d''exécution
----------------------------------------------------------
Plan hash value: 3986965708
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     2 |    54 |   179   (2)| 00:00:03 |
|   1 |  VIEW                              |                              |     2 |    54 |   179   (2)| 00:00:03 |
|   2 |   HASH UNIQUE                      |                              |     2 |   176 |   179   (2)| 00:00:03 |
|*  3 |    HASH JOIN                       |                              |     2 |   176 |   178   (1)| 00:00:03 |
|*  4 |     TABLE ACCESS BY INDEX ROWID    | REA_NOTIF_NTW_BANDE          |     1 |    15 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                  |                              |    38 |  2204 |   162   (0)| 00:00:02 |
|   6 |       NESTED LOOPS                 |                              |    41 |  1763 |    39   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                              |     2 |    66 |     5   (0)| 00:00:01 |
|   8 |         TABLE ACCESS BY INDEX ROWID| REA_SERVICE                  |     1 |    18 |     2   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN         | IDX_REA_SERVICE_PK           |     1 |       |     1   (0)| 00:00:01 |
|  10 |         TABLE ACCESS BY INDEX ROWID| REA_NOTIF_NTW_SAVEGROUP      |     2 |    30 |     3   (0)| 00:00:01 |
|* 11 |          INDEX RANGE SCAN          | IDX_REA_SAVEGROUP_FK_SERVICE |     2 |       |     1   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | REA_NOTIF_NTW_SAVESET        |    25 |   250 |    21   (0)| 
|* 13 |         INDEX RANGE SCAN           | IDX_REA_SAVESET_FK_SAVEGROUP |    25 |       |     2   (0)| 00:00:01 |
|* 14 |       INDEX RANGE SCAN             | IDX_REA_BANDE_FK_SAVESET     |     1 |       |     2   (0)| 00:00:01 |
|  15 |     INDEX FAST FULL SCAN           | IDX_EXPORTBANDE_HEUREBANDE   | 14126 |   413K|    15   (0)| 00
----------------------------------------------------------------------------------------------------

J'ai simplement enlevé de la sélection le champs 'statut', et il utilise l'index.
J'ai naïvement créé un index identique en y ajoutant le champs statut, puis j'ai relancé la requête avec le champs 'statut', mais il ne l'utilise toujours pas

Peut être qu'il me faudrait plus de volumétrie pour tester la pertinence de mes index. N'étant pas expert, je voudrai éviter d'en créer trop.

Merci pour ta réponse Waldar.

**mnitu** · 26/03/2009, 20h51

Envoyé par Snipah

...
J'ai testé votre requête, elle utilise bien l'index :
Le cout est par contre supérieure à la précédente requête qui ne l'utilise pas, ça expliquerait qu'il ne soit pas utilisé dans ce cas?

C'est ça, vous avez bien compris.

Envoyé par Snipah

...

J'ai simplement enlevé de la sélection le champs 'statut', et il utilise l'index.

Dans ce cas il utilise l'index comme une table (INDEX FAST FULL SCAN) donc ça revient à dire que le table scan est meilleur.

Envoyé par Snipah

...
J'ai naïvement créé un index identique en y ajoutant le champs statut, puis j'ai relancé la requête avec le champs 'statut', mais il ne l'utilise toujours pas

...

Ce qui confirme que pour l'optimiseur faire un table scan est toujours meilleur par rapport aux informations (statistiques) disponibles.

D'autre part avez vous pris soin de recalculer les statistiques après avoir crée et récrée l'index ?

Si vous avez le temps lisez aussi cet article.

**Snipah** · 27/03/2009, 10h19

Oui, j'avais bien recalculé les stats au niveau de l'index (COMPUTE STATISTICS lors de la création, vous parlez bien de ça?).
Je suis en train de lire l'article, merci pour le lien et les explications

Utilisation d'un index

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager