Index non utilisé dans une jointure

**lasyan3** · 31/03/2011, 13h37

Bonjour,

Je rencontre un problème lié à la bonne utilisation des index sous Oracle 10g.

Je dispose d'une table case_variable :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE CASE_VARIABLE
(
  TCID        NUMBER(10)                        NOT NULL,
  VARIABLE    VARCHAR2(64 BYTE)                 NOT NULL,
  TYPE        NUMBER(5)                         NOT NULL,
  RANK        NUMBER(5),
  VALUE       VARCHAR2(2000 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

A laquelle j'ai ajouté l'index suivant :

Code :

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

1
2
3
4
CREATE INDEX INX_TCID_VAR_RK ON CASE_VARIABLE
(TCID, VARIABLE, RANK)
NOLOGGING
NOPARALLEL;

Je précise que la table ne possède pas de clef primaire, je ne sais pas si ça a de l'importance pour la suite.

Je souhaite récupérer une liste de données, j'utilise la requête suivante :

Code :

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

1
2
3
SELECT   w.id, obj.VALUE
FROM      workcase w
INNER JOIN case_variable obj ON obj.tcid = w.id AND obj.variable = 'objet_dde';

Dans ce cas, il se trouve que l'index n'est pas utilisé (j'utilise le paramètre MONITORING et la table V$OBJECT_USAGE pour m'en assurer). Et je me demande bien pourquoi !

Car si j'exécute la requête suivante, l'index est correctement utilisé :

Code :

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

1
2
3
SELECT   *
  FROM   case_variable
 WHERE   tcid = 288069 AND variable = 'objet_dde';

Je précise que, n'étant pas admin de la base, je ne peux malheureusement pas faire de "explain plan".

Merci d'avance.

**ojo77** · 31/03/2011, 13h47

Dans le second cas, la jointure est faite sur w.id et non sur une valeur fixe, selon le nombre distinct de w.id , l'optimiseur va estimer devoir remonter un certain nombre de blocs de case_variable et de l'index si effectivement il passe par l'index. Si ce nombre de blocs est supérieur au nombre total de blocs de la table case_variable il va préférer le full table scan.

Si tu changes l'ordre des colonnes dans l'index en mettant varaiable en premier et que la colonne variable de la table case_variable est suffisamment sélective le résultat peut être différent.

Si tu ajoutes la colonne value à l'index, il ne passera plus par la table case_variable mais seulement par l'index, cependant la taille de l'index va exploser.

**mnitu** · 31/03/2011, 14h22

Postez la description de la table workcase. Postez le résultat de la requête dans sqlplus avec autotrace on pour voir le plan d’exécution.
Bon, Oracle n’utilise pas l’index mais c’est quoi votre problème ?

**lasyan3** · 31/03/2011, 16h13

Merci pour votre aide.

Voici le script de la table workcase :

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
Prompt Table WORKCASE;
CREATE TABLE W4.WORKCASE
(
  ID                     NUMBER(10)             NOT NULL,
  NAME                   VARCHAR2(64 BYTE)      NOT NULL,
  PROCEDURE_ID           NUMBER(10),
  INITIATOR_ID           NUMBER(10),
  RESPONSIBLE_ID         NUMBER(10),
  RESP_ROLE_ID           NUMBER(10),
  PRIORITY               NUMBER(3),
  ICN_ID                 NUMBER(10),
  STATE                  NUMBER(5),
  CREATION_DATE          DATE,
  OVERDUE_DATE           DATE,
  ALARM_DATE             DATE,
  END_DATE               DATE,
  PARENT_TASK_ID         NUMBER(10),
  SERVER_PARENT_TASK_ID  NUMBER(10),
  OVERDUE_DETECTED       NUMBER(1),
  ALARM_DETECTED         NUMBER(1)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
 
 
Prompt Index INX_CASE_ALARM;
CREATE INDEX W4.INX_CASE_ALARM ON W4.WORKCASE
(ALARM_DATE, ALARM_DETECTED)
LOGGING
NOPARALLEL;
 
 
Prompt Index INX_CASE_OVERDUE;
CREATE INDEX W4.INX_CASE_OVERDUE ON W4.WORKCASE
(OVERDUE_DATE, OVERDUE_DETECTED)
LOGGING
NOPARALLEL;
 
 
Prompt Index INX_CASE_RESP_ROLE;
CREATE INDEX W4.INX_CASE_RESP_ROLE ON W4.WORKCASE
(RESP_ROLE_ID)
LOGGING
NOPARALLEL;
 
 
Prompt Index INX_CASE_STATE;
CREATE INDEX W4.INX_CASE_STATE ON W4.WORKCASE
(STATE)
LOGGING
NOPARALLEL;
 
 
Prompt Index INX_PROCEDURE_WORKCASE;
CREATE INDEX W4.INX_PROCEDURE_WORKCASE ON W4.WORKCASE
(PROCEDURE_ID)
LOGGING
NOPARALLEL;
 
 
Prompt Non-Foreign Key Constraints on Table WORKCASE;
ALTER TABLE W4.WORKCASE ADD (
  PRIMARY KEY
 (ID),
  UNIQUE (NAME));
 
Prompt Foreign Key Constraints on Table WORKCASE;
ALTER TABLE W4.WORKCASE ADD (
  FOREIGN KEY (PROCEDURE_ID) 
 REFERENCES W4.PROCEDURES (ID),
  FOREIGN KEY (INITIATOR_ID) 
 REFERENCES W4.ACTOR (ID),
  FOREIGN KEY (RESPONSIBLE_ID) 
 REFERENCES W4.ACTOR (ID),
  FOREIGN KEY (RESP_ROLE_ID) 
 REFERENCES W4.CATEGORY (ID),
  FOREIGN KEY (SERVER_PARENT_TASK_ID) 
 REFERENCES W4.SERVER (ID));

J'ai bien tenté de faire un autotrace, mais j'obtiens des erreur (probablement car je n'ai pas suffisamment de droits, je suis en train de travailler un dba de la société pour résoudre ça ^^) :

Code :

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

1
2
3
4
5
6
SQL> set autotrace on;
SP2-0613: Impossible de vérifier le format ou l'existence de PLAN_TABLE
SP2-0611: Erreur lors de l'activation de l'état EXPLAIN
SP2-0618: Impossible de trouver l'identificateur de session. Le rôle PLUSTRACE doit être activé.
SP2-0611: Erreur lors de l'activation de l'état STATISTICS
SQL>

C'est pour cette raison que j'utilise le paramètre MONITORING de l'index, en attendant.

Mon problème, concrètement, consiste à réduire le temps de traitement de mes requêtes. Ma requête avec jointure met quelques secondes à s'exécuter, je me disais donc qu'avec un index judicieusement placé sur la table case_variable, qui contient 2 555 954 de lignes, me permettrait de réduire sensiblement le temps d'exécution.

**mnitu** · 31/03/2011, 18h21

Quelle version d'Oracle ?
Creating a PLAN_TABLE
Creating the PLUSTRACE Role

**pachot** · 01/04/2011, 00h01

Bonjour,

La différence entre

Code :

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

1
2
3
SELECT   w.id, obj.VALUE
FROM      workcase w
INNER JOIN case_variable obj ON obj.tcid = w.id AND obj.variable = 'objet_dde';

et:

Code :

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

1
2
3
SELECT   *
  FROM   case_variable
 WHERE   tcid = 288069 AND variable = 'objet_dde';

c'est que dans le 2ème cas tu ne va voir qu'une seule valeur de tcid alors que dans le 1er cas tu y vas autant de fois que tu as de lignes dans la table workcase. Et donc plutôt que de descendre l'index à chaque fois, Oracle préfère monter toute la table dans une hash table puis de faire la jointure dessus. C'est pas forcément une erreur.

Cordialement,
Franck.

**lasyan3** · 01/04/2011, 09h47

Envoyé par mnitu

Quelle version d'Oracle ?
Creating a PLAN_TABLE
Creating the PLUSTRACE Role

C'est une base Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production.

Au passage, merci pour les liens, je ne savais pas qu'on pouvait simplement créer sa propre table.
du coup, voici le résultat de l'explain plan :

Code :

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

1
2
3
4
5
6
STATEMENT_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	OTHER
 
	01/04/2011 09:41:19		SELECT STATEMENT							CHOOSE		0		1989	1989	6295	182555		(Memo)
	01/04/2011 09:41:19		NESTED LOOPS									1	0	1	1989	6295	182555		(Memo)
	01/04/2011 09:41:19		TABLE ACCESS	FULL		W4	CASE_VARIABLE	2		ANALYZED		2	1	1	1989	6295	151080		(Memo)
	01/04/2011 09:41:19		INDEX	UNIQUE SCAN		W4	SYS_C002765		UNIQUE	ANALYZED	1	3	1	2		1	5		(Memo)

On remarque bien que l'index n'est pas utilisé car oracle fait un ACCESS FULL sur la table case_variable. Par contre, il utilise ensuite l'index C002765 qui est sur la colonne ID de la table workcase (normal donc).

pachot --> je comprend ce que tu me dis, mais du coup, si je ne peux pas m'appuyer sur un index pour accélérer ma requête, de quel autre moyen je dispose ?

**ojo77** · 01/04/2011, 10h24

On a obj.tcid = w.id

La requête peut donc s'écrire

Code :

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

1
2
3
4
5
SELECT obj.tcid
     , obj.VALUE
FROM workcase w
INNER JOIN case_variable obj 
        ON obj.tcid = w.id AND obj.variable = 'objet_dde';

si obj.tcid est une fk sur w.id on peut réécrire

Code :

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

1
2
3
4
5
SELECT obj.tcid
     , obj.VALUE
FROM case_variable obj 
where obj.variable = 'objet_dde'
  AND obj.tcid is not null;

**lasyan3** · 01/04/2011, 10h32

Envoyé par ojo77

La requête peut donc s'écrire

Code :

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

1
2
3
4
5
SELECT obj.tcid
     , obj.VALUE
FROM workcase w
INNER JOIN case_variable obj 
        ON obj.tcid = w.id AND obj.variable = 'objet_dde';

Certes, mais ça n'a aucune incidence.
Par contre, j'ai remarqué qu'en ne mettant que "w.id" dans le select, l'explain plan montre que l'index est utilisé :

Code :

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

1
2
3
4
5
6
STATEMENT_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	OTHER
 
	01/04/2011 10:29:48		SELECT STATEMENT							CHOOSE		0		5	5	6295	144785		(Memo)
	01/04/2011 10:29:48		NESTED LOOPS									1	0	1	5	6295	144785		(Memo)
	01/04/2011 10:29:48		INDEX	FAST FULL SCAN		W4	INX_TCID_VAR_RK		NON-UNIQUE			2	1	1	5	6295	113310		(Memo)
	01/04/2011 10:29:48		INDEX	UNIQUE SCAN		W4	SYS_C002765		UNIQUE	ANALYZED	1	3	1	2		1	5		(Memo)

Mais la requête n'en est pas plus rapide pour autant (sans doute parce que l'index est utilisé en FAST FULL SCAN ? ).

**mnitu** · 01/04/2011, 10h39

L'index n'est pas utilisé parce qu'il n'a pas été analysé. Recalculez les statistiques avec DBMS_STATS.

**lasyan3** · 01/04/2011, 11h03

J'ai passé le script suivant :

Code :

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

dbms_stats.gather_schema_stats(ownname=>'W4', cascade=>TRUE) ;

Mais ça ne change rien

**ojo77** · 01/04/2011, 11h11

Y a-t-il dans case_variable des valeurs de tcid qui ne correspondent pas à des valeurs de id dans workcase ?

**lasyan3** · 01/04/2011, 11h39

Oui, car en fait dans l'application on a des dossiers et des tâches à effectuer pour chaque dossier (donc plusieurs tâches par dossier).
Et la table case_variable contient les variables aussi bien des dossiers que des taches.

**ojo77** · 01/04/2011, 11h49

Dans ce cas, il va être difficile de faire plus rapide que ce plan

Envoyé par lasyan3

Code :

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

1
2
3
4
5
6
STATEMENT_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS	OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_INSTANCE	OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID	POSITION	COST	CARDINALITY	BYTES	OTHER_TAG	OTHER
 
	01/04/2011 10:29:48		SELECT STATEMENT							CHOOSE		0		5	5	6295	144785		(Memo)
	01/04/2011 10:29:48		NESTED LOOPS									1	0	1	5	6295	144785		(Memo)
	01/04/2011 10:29:48		INDEX	FAST FULL SCAN		W4	INX_TCID_VAR_RK		NON-UNIQUE			2	1	1	5	6295	113310		(Memo)
	01/04/2011 10:29:48		INDEX	UNIQUE SCAN		W4	SYS_C002765		UNIQUE	ANALYZED	1	3	1	2		1	5		(Memo)