Index non utilisé dans une jointure

Version imprimable

Bonjour,

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

Je dispose d'une table case_variable :
Code:

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:

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:

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:

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.

31/03/2011, 13h47
ojo77

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.
31/03/2011, 14h22
mnitu

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 ?

Merci pour votre aide.

Voici le script de la table workcase :

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
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:

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.

31/03/2011, 18h21
mnitu

Quelle version d'Oracle ?
Creating a PLAN_TABLE
Creating the PLUSTRACE Role
01/04/2011, 00h01
pachot
Bonjour,

La différence entre
Code:

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:

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.

Citation:

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:

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 ?

On a obj.tcid = w.id

La requête peut donc s'écrire

Code:

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:

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;

Citation:

Envoyé par ojo77

La requête peut donc s'écrire

Code:

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:

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 ? ).

01/04/2011, 10h39
mnitu

L'index n'est pas utilisé parce qu'il n'a pas été analysé. Recalculez les statistiques avec DBMS_STATS.
01/04/2011, 11h03
lasyan3

J'ai passé le script suivant :

Code:

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

Mais ça ne change rien :(
01/04/2011, 11h11
ojo77

Y a-t-il dans case_variable des valeurs de tcid qui ne correspondent pas à des valeurs de id dans workcase ?
01/04/2011, 11h39
lasyan3

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.

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

Citation:

Envoyé par lasyan3

Code:

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)

01/04/2011, 14h00
mnitu

Est-ce que la condition

Code:

AND variable = 'objet_dde'

est assez sélective ?
12/04/2011, 09h06
lasyan3

Citation:

Envoyé par ojo77

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

C'est aussi ce que m'a dit un dba de la boite. Du coup, je vais abandonner l'idée. Merci quand même pour votre aide à tous.