Jointure, index et select imbriqué

**Knonix** · 03/07/2008, 15h55

Voila mon soucis :

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

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

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 :

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

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

**orafrance** · 03/07/2008, 16h18

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

**Knonix** · 03/07/2008, 16h28

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

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

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

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

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'
 
********************************************************************************

**orafrance** · 03/07/2008, 17h07

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

**Knonix** · 03/07/2008, 17h40

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 :

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

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 :

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

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.

**Knonix** · 03/07/2008, 17h55

J'ai refait des explina plans via toad 9:

Code :

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

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 :

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

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'

**orafrance** · 03/07/2008, 17h55

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

essaye :

Code :

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

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

Jointure, index et select imbriqué

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager