Jointure simple qui ne passe pas par les index ?! [Résolu]

[souch]

Bonjour à tous,

je débarque sous Oracle (pas facile ) et tente de passer une appli de firebird ) à Oracle 10g, en commençant mes tests, je fais direct une syncope en voyant le temps requis pour une requête toute bête sur de petites tables ...

J'ai certainement fait une erreur grosse comme un éléphant quelque part, voici donc la structure des mes deux tables, ainsi que la requête incriminée :

Temps d'exécution : entre 0.35s et 0.60s en local sur le serveur via Sql developer.

Table E_Examens (27500 lignes)
PrimaryKey integer (number(*,0) sur E_EXAMENS.IDEXAMEN
index integer (number(*,0) sur E_EXAMENS.IDPATIENT
index Asc timestamp(0) sur E_EXAMENS.DateExamen
ForeignKey (Cascade) sur p_patients.idpatient

Table P_Patients (20000 lignes)
PrimaryKey integer (number(*,0) sur P_PATIENTS.IDPATIENT
index varchar2(32 char) sur P_PATIENTS.Nom

Requête :

Code :

1
2
3
4
SELECT p.Nom, e.DATEEXAMEN
FROM E_EXAMENS e
INNER JOIN P_PATIENTS p ON p.idpatient = e.idpatient
ORDER BY e.DateExamen ASC

Plan :

Code :

1
2
3
4
5
6
"Optimizer"	"Cost"	"Cardinality"	"Bytes"	"Partition Start"	"Partition Stop"	"Partition Id"	"ACCESS PREDICATES"	"FILTER PREDICATES"
"SELECT STATEMENT"	"ALL_ROWS"	"1123"	"26471"	"2673571"	""	""	""	""	""
"SORT(ORDER BY)"	""	"1123"	"26471"	"2673571"	""	""	""	""	""
"HASH JOIN"	""	"507"	"26471"	"2673571"	""	""	""	""P"."IDPATIENT"="E"."IDPATIENT""	""
"TABLE ACCESS(FULL) MEDIREPORT.E_EXAMENS"	""	"249"	"26470"	"582340"	""	""	""	""	""
"TABLE ACCESS(FULL) MEDIREPORT.P_PATIENTS"	""	"97"	"22341"	"1764939"	""	""	""	""	""

Pourquoi le plan ne tient t'il point compte de mes index ?

Merci bcp à tout ceux qui prendront le temps de se poser la question

Souch

[orafrance]

tout simplement parce que tu sélectionnes toutes les lignes des 2 tables

Si tu rajoutes une jointure qui limite le résultat, tu devrais utiliser un index

[souch]

Salut OraFrance,

Merci pour ta réponse, en effet ca aide de limiter le nombre de retour, cependant mes requettes gardaient des plans foireux (tout en natural, pas ou peut d'index utilisés).
Je viens de trouver la solution :
passer le optimizer_mode en mode 'RULE' (http://www.adp-gmbh.ch/ora/tuning/optimizer.html), je n'ai pas encore assez potassé pour piger pourquoi ca marche mieux avec, mais le résultat est équivoque : toutes mes requetes sont a présent faites en passant exclusivement par les indexs

[orafrance]

si c'est le cas ça veut dire que tes stats ne sont pas à jour. Le mode RULE est à éviter SURTOUT en 10g

[souch]

ha ? Pourtant c'est diablement efficace ^^
Question bête, comment mettre à jours mes stats ?
cette méthode est elle la bonne ?

Code :

analyze TABLE <TABLE name> compute statistics;

Edit : après avoir passé le compute statistics sur toute mes tables et être repassé en mode 'ALL_ROWS', mes plans sont toujours faux (quasiment tout en access full), si je repasse en CHOOSE ou RULE, c'est impec...

[orafrance]

un beau plan d'exécution n'est pas forcément synonyme de performance. RULE est obsoléte. Quelle tête elle a ta requête maintenant ? Combien de lignes tu récupères ?

et ANALYZE aussi est obsoléte, tu dois utiliser DBMS_STATS.

[souch]

Merci pour ton aide

ANALYZE aussi est obsoléte

rhaa, décidément je n'utilise que des fonctions obsolètes moi ^^

la 1er requête tournait bien après avoir ajouté quelques filtres limitant le nombre de retours, et renvoyais le même temps d'exec quelque soit le mode.

Code :

1
2
3
4
5
6
7
8
9
SELECT * FROM
 ( SELECT rownum RNUM, p.Nom, e.DATEEXAMEN 
    FROM E_EXAMENS e 
    INNER JOIN P_PATIENTS p ON p.idpatient = e.idpatient 
    AND UPPER(P.NOM) LIKE Upper('%BE%')
    ORDER BY e.DateExamen ASC
 )
WHERE (RNUM <= 20) AND (RNUM >= 10)
// Vitesse accéptable

cepandant avec des requettes + importantes, mon probleme reste entier :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT * FROM
 ( SELECT rownum RNUM,
  STO.*,CAT.(...), PAT.(...), SMAT.(...),EXAM.(...)
 
FROM STOCK STO
INNER JOIN CATALOGUE CAT ON CAT.LOCALID=STO.CAT_IID
INNER JOIN MATERIEL SMAT ON SMAT.STO_ID=STO.STO_ID AND SMAT.USAGE>0
INNER JOIN E_EXAMENS EXAM ON EXAM.IDEXAMEN = STO.ID_OUT
INNER JOIN P_PATIENTS PAT ON PAT.IDPATIENT=EXAM.IDPATIENT
INNER JOIN E_OPERATEURS OPS ON OPS.IDPATIENT=EXAM.IDPATIENT 
           AND OPS.IDEXAMEN = EXAM.IDEXAMEN AND OPS.IDPRINC=1
INNER JOIN G_IDENTITES IDE ON IDE.ididentite = OPS.ididentite
 
WHERE STO.TYPE_OUT='P' AND STO.DATE_OUT IS NOT NULL AND STO.ATTVALIDE=0  
)
WHERE (RNUM <= 500)

MODE RULE = entre 0.060 et 0.100 sec
MODE ALL_ROWS = entre 1 et 1.5 sec

la totalité des champs utilisés pour les jointures et dans la clause where sont des index (pk pour les jointures).

Ce qui me chagrine, c'est que la même requete sur la même structure, indexs et données sous Firebird 1.5 ne me posent aucun problèmes et réponde bcp + vite qu'oracle moi qui croyais que migrer dessus allais tout speeder

[orafrance]

essaye :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT /*+ FIRST_ROWS */
  STO.*,CAT.(...), PAT.(...), SMAT.(...),EXAM.(...)
 FROM STOCK STO
INNER JOIN CATALOGUE CAT ON CAT.LOCALID=STO.CAT_IID
INNER JOIN MATERIEL SMAT ON SMAT.STO_ID=STO.STO_ID AND SMAT.USAGE>0
INNER JOIN E_EXAMENS EXAM ON EXAM.IDEXAMEN = STO.ID_OUT
INNER JOIN P_PATIENTS PAT ON PAT.IDPATIENT=EXAM.IDPATIENT
INNER JOIN E_OPERATEURS OPS ON OPS.IDPATIENT=EXAM.IDPATIENT 
           AND OPS.IDEXAMEN = EXAM.IDEXAMEN AND OPS.IDPRINC=1
INNER JOIN G_IDENTITES IDE ON IDE.ididentite = OPS.ididentite
 
WHERE STO.TYPE_OUT='P' AND STO.DATE_OUT IS NOT NULL AND STO.ATTVALIDE=0
AND ROWNUM <= 500

que donne l'explain plan ?


et en passant, l'application d'une fonction sur une colonne ne permet pas d'utiliser l'index. Ainsi dans la jointure :

Code :

AND UPPER(P.NOM) LIKE Upper('%BE%')

si il y a un index sur NOM, il ne sera pas utiliser. Il faut faire un index de fonction éventuellement, l'idéal étant de stocker les noms en majuscules pour éviter le UPPER

[souch]

l'application d'une fonction sur une colonne ne permet pas d'utiliser l'index

merci bcps, cette info va mettre très utile !!

Effectivement, en FIRST_ROWS ca passe très bien , cela veut-il dire que pour chaque requête je doit choisir entre le mode standart (ALL_ROWS) et celui ci, et modifier toutes mes requêtes (plusieurs centaines... ) en leur appliquant le bon mode ?

[souch]

PS : En stockant les noms en maj, je perd les accents ... (désolé multi post, j'ai fait citation au lieu d'edition)

[orafrance]

tu peux le modifier au niveau des paramètres de la base

FIRST_ROWS permet d'indiquer à Oracle qu'il doit s'attacher à récupérer les 1° lignes plus rapidement alors que ALL_ROWS permet à l'optimiseur de récupérer toutes les lignes avant de faire le tri et renvoyer le résultat.

Au moins, là tu restes dans les précos Oracle

[orafrance]

Envoyé par souch

PS : En stockant les noms en maj, je perd les accents ...

alors tu peux remplacer l'index par un index de fonction

[souch]

Oki,
je configure donc ma base pour qu'elle soit en FIRST_ROWS par défaut, et je refait le tour de mes tables pour ajouter les indexs manquants et en transformer un certain nombre en indexs de fonction :-)

Merci pour ton aide précieuse !

Souch

[orafrance]

attention quand même, trop d'index tue l'index