Jointure, index et select imbriqué

Voila mon soucis :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Table : TSTOCK ( STDATE DATE NOT NULL, MATID NUMBER (5) NOT NULL, EMPID NUMBER (10) NOT NULL, SITID NUMBER (10) NOT NULL, QUANTITE NUMBER (15,3), SMATORIG VARCHAR2 (10) NOT NULL, MID NUMBER (5), SIT_NOM VARCHAR2 (30)) PRIMARY KEY ( STDATE, MATID, EMPID, SMATORIGINE ) INDEX ON TSTOCK(MATID) INDEX ON TSTOCK(EMPID) INDEX ON TSTOCK(SMATORIGINE) INDEX ON TSTOCK(SITID) INDEX ON TSTOCK(SIT_NOM) La table et les index sont dans 2 tablespace séparé.

---

Code:

---

1 2 3 4 5

Requete : SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( select max(M.stdate) from TSTOCK M WHERE M.SITID = S.SITID AND M.MID = 7 AND M.SMFM_ID = 10000) group by S.SSIT_NOM

---

Cette requête met plus de 45 mn sous Oracle 9.2.0.8 (analise a été réalisé sur les tables et index) alors que la même requête sous une base identique sous Oracle 8.1.7.4 met 16 s. (les explains plans me montrent bine que dans un cas on fait un scan full et dans l'autre on utilise les index)

Code:

---

1 2 3 4 5

Requete : SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( select max(M.stdate) from TSTOCK M WHERE M.MID = 7 AND M.SMFM_ID = 10000) group by S.SSIT_NOM

---

Par contre cette même requête sous Oracle 9 mais cette fois sans la jointure sur le SITID me permet d'avoir un temps de réponse identique que sous Oracle 8.

Donc ma question me manquerait'il pas un index plus efficace que la CBO d'Oracle 9 considère comme nécessaire pour le bon déroulement de la requête
et si oui lequel ?

Merci

PS : Dans le forum administration j'avais déjà interrogé sur la question car pour moi au départ, c'était un problème entre Oracle9 et ORacle 8 mais là je décide de prendre le problème sous un autre angle celui de la requête.

MErci

ce serait pas mal de commencer par nous montrer les 4 explains plan et nous donner la liste des indexes avec les colonnes qui les composes :?

Bha les explains plans comme je l'ai dit indique soit un scan full soit l'utilisation des indexes (index que j'ai marqué dans la description de la table).
C'est marrant que je tombe toujours sur toi orafrance :D . C'est toujours le même soucis avec Oracle 8.1.7 et Oracle 9.2 sauf que cette fois j'ai basculé sur sur 9.2.0.8 et que la seule amélioration c'est que je peut analyser mon schéma (par rapport à la 9.2.0.4) mais j'ai toujours le même soucis sur cette requête. Donc je me dit que la CBO et les paramétrages n'ont rien à voir dans tout cela mais bien la conception de la base.
Les explians plans les voila :
Sous ORacle 8:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 15 SORT (GROUP BY) 36 FILTER 38124 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TSTOCK' 38124 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'TSTOCK_FK' (NON-UNIQUE) 30 SORT (AGGREGATE) 38123 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'TSTOCK' 38138 AND-EQUAL 90759 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'TSTOCK_FK' (NON-UNIQUE) 78832 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'TSTOCK_FK3' (NON-UNIQUE)

---

sous ORacle 9

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (GROUP BY) 0 VIEW 0 FILTER 0 SORT (GROUP BY) 0 HASH JOIN 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTOCK' 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TSTOCK'   ********************************************************************************

---

il y a combien de lignes dans tes tables et combien de lignes sont comptées par l'optimiseur (colonne num_rows de all_tables) ?

La colonne NUM_ROWS de cette table dans ALL_TABLES donne la valeur 1694808, qui correspond bien à la valeur que me donne une select count(*) sur la table.
La valeur du last_annalyzed de cette table est à aujourd'hui.

Je rappelle il y a qu'une seule table on fait un select tata from table where col = (select max col from table where table = table)

Code:

---

1 2 3 4 5

SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( SELECT max(M.stdate) FROM TSTOCK M WHERE M.SITID = S.SITID AND M.MID = 7 AND M.SMFM_ID = 10000) GROUP BY S.SIT_NOM

---

45 mn et

Code:

---

1 2 3 4 5

SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( SELECT max(M.stdate) FROM TSTOCK M WHERE M.MID = 7 AND M.SMFM_ID = 10000) GROUP BY S.SIT_NOM

---

4 s il n 'y a de différence que la jointure de la table sur elle même avec M.SITID = S.SITID, colonne sur laquelle existe un index.

J'ai refait des explina plans via toad 9:

Code:

---

1 2 3 4 5 6 7 8 9 10

Plan SELECT STATEMENT CHOOSECost: 95,451 Bytes: 315 Cardinality: 21 7 SORT GROUP BY Cost: 95,451 Bytes: 315 Cardinality: 21 6 VIEW SYS. Cost: 95,451 Bytes: 5,925 Cardinality: 395 5 FILTER 4 SORT GROUP BY Cost: 95,451 Bytes: 15,800 Cardinality: 395 3 HASH JOIN Cost: 9,153 Bytes: 899,274,960 Cardinality: 22,481,874 1 TABLE ACCESS FULL PINF.TSTOCK Cost: 4,570 Bytes: 325,920 Cardinality: 21,728 2 TABLE ACCESS FULL PINF.TSTOCK Cost: 4,570 Bytes: 543,200 Cardinality: 21,728

---

Explain lorsque l'on a S.SITID=M.SITID et 45 mn

Code:

---

1 2 3 4 5 6 7 8

Plan SELECT STATEMENT CHOOSECost: 19 Bytes: 54 Cardinality: 3 5 SORT GROUP BY Cost: 19 Bytes: 54 Cardinality: 3 4 TABLE ACCESS BY INDEX ROWID PINF.TSTOCK Cost: 8 Bytes: 54 Cardinality: 3 3 INDEX RANGE SCAN UNIQUE PINF.PK_TSTOCK Cost: 3 Cardinality: 10 2 SORT AGGREGATE Bytes: 13 Cardinality: 1 1 TABLE ACCESS FULL PINF.TSTOCK Cost: 4,570 Bytes: 282,464 Cardinality: 21,728

---

Explain lorsque sans S.SITID=M.SITID et 4'

les indexes utilisés sont comment ? T'en a bcp je trouve :koi:

essaye :

Code:

---

1 2 3 4

SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( SELECT max(M.stdate) FROM TSTOCK M WHERE M.SITID = S.SITID AND M.MID = S.MID AND M.SMFM_ID = S.SMFM_ID) GROUP BY S.SSIT_NOM

---

la PK c'est tout l'ensemble ( STDATE, MATID, EMPID, SMATORIGINE ) ?

BINGO !!! Orafrance t'es un champion
Oui la PK est sur les 4 champs et du coup j'ai tenté de modifier ma requête en prenant comme jointure les 4 champs de la PK, et ça fonctionne, cette fois il prend correctement l'index et plus que 8seconde.

Code:

---

1 2 3 4 5 6 7 8 9 10 11

SELECT S.SIT_NOM, sum(S.QUANTITE) FROM TSTOCK S WHERE S.SMFM_ID = 10000 AND S.MID = 7 AND S.STDATE = ( SELECT max(M.stdate) FROM TSTOCK M WHERE M.SITID = S.SITID AND M.EMPID = S.EMPID AND M.SMATORIGINE = S.SMATORIGINE AND M.stdate = S.stdate AND M.MID = S.MID AND M.SMFM_ID = 10000) GROUP BY S.SSIT_NOM

---

Donc cette fois et de maniére définitive je mets en résolu ce sujet mais avant de ne plsu y revenir comment cela ce fait'il qu'en oracle8 cela était OK et qu'en ORacle9 ça ne fonctionnait plus ? (Oracle 8 au niveau du CBO était plus persmissif ?)
MErci pour tout et de ta patience orafrance.

remplace AND M.SMFM_ID = 10000 par AND M.SMFM_ID = S.SMFM_ID et ce sera parfait :king:

En 8 tu utilisais le mode RULE non ?