Requête qui prend des plombes

[Kiroukool]

Bonjour

J'essaye de faire une requete pas tres compliquée, mais sur 2M de lignes, ce qui fait qu'elle prend beaucoup de temps.

J'ai donc comme requête :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT   t.code_entrep,
         p.code_qual,
         p.n_num_quantite,
         t.date_mep
    FROM table_stockage t JOIN quantite p ON (t.code_commun = p.code_commun)
   WHERE t.date_mep = (SELECT MAX (date_mep)
                              FROM table_stockage t2
                             WHERE t2.code_entrep = t.code_entrep
                               AND t2.code_produit = t.code_produit)
ORDER BY code_entrep;

J'ai ajouté un index sur ma table table_stockage sur les colonnes code_entrep et code_produit


Et comme plan d'execution j'obtiens

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                   | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         | 20110 |  1060K|  2084 |
|   1 |  SORT ORDER BY                 |                         | 20110 |  1060K|  2084 |
|   2 |   FILTER                       |                         |       |       |       |
|   3 |    HASH JOIN                   |                         | 20110 |  1060K|  1867 |
|   4 |     TABLE ACCESS FULL          | QUANTITE                    |   629 | 10693 |     2 |
|   5 |     TABLE ACCESS FULL          | TABLE_STOCKAGE      | 19662 |   710K|  1863 |
|   6 |    SORT AGGREGATE              |                         |     1 |    18 |       |
|   7 |     TABLE ACCESS BY INDEX ROWID| TABLE_STOCKAGE      |     1 |    18 |     2 |
|   8 |      INDEX RANGE SCAN          | TABLE_STOCKAGE_IND  |     1 |       |     1 |
------------------------------------------------------------------------------------------

Merci pour votre aide

[LeoAnderson]

y'a-til un index sur code_commun de quantité ?

[orafrance]

Ouais... encore le jeu "devine la version d'Oracle et les indexes sur les tables"... j'adore

Commençons par le plus évident
Ceci ne ferait-il pas l'affaire :

Code :

1
2
3
4
5
6
SELECT   t.code_entrep,
         p.code_qual,
         p.n_num_quantite,
         MAX(t.date_mep)
    FROM table_stockage t JOIN quantite p ON (t.code_commun = p.code_commun)
GROUP BY code_entrep,code_qual,n_num_quantite;

[orafrance]

Envoyé par LeoAnderson

y'a-til un index sur code_commun de quantité ?

et un autre sur date_mep

Je dirais que là y'en a un sur code_commun mais de table_stockage

[Kiroukool]

Non il n'y en avait pas.

J'ai essayé en en rajoutant un, mais c'est toujours tres long

[orafrance]

t'as calculé les stats ? Et puis c'est confidentielle la version et la liste des indexes ?

[Kiroukool]

Envoyé par orafrance

Ouais... encore le jeu "devine la version d'Oracle et les indexes sur les tables"... j'adore

Commençons par le plus évident
Ceci ne ferait-il pas l'affaire :

Code :

Non ca ne fait pas l'affaire car ca me récupère tous les t.code_entrep,
p.code_qual,
p.n_num_quantite de toutes les lignes ou j'ai une date_mep.

Je veux juste récupèrer ces valeurs la pour la derniere date_mep de chaque enregistrement

[orafrance]

J'AI DEMANDE LA VERSION DE LA BASE !!!!

[Kiroukool]

Version d'oracle

oracle 9i 9.2.0.6.0

Les index existants sur les tables utilisées sont

1) sur quantite :

une primary key sur un champs qui n'est pas utilisé ici
un index sur code_qual
un index sur n_num_quantite

2) sur table_stockage :

juste deux index sur code_entrep et code_produit


Edit : du calme...

[orafrance]

donc tu n'as ni d'index sur quantité.code_commun suggéré par léo ni sur table_stockage.date_mep que j'ai proposé... j'ai pas l'impression d'écrire à un robot c'est très agréable

[orafrance]

en tout cas, t'as pas d'index sur les colonnes qui servent à faire les jointures alors faut pas s'étonner que ça rame

[Kiroukool]

Lis mes posts j'ai rajouté un des deux index mais ca n'a rien changé...

Je rajoute celui que tu as suggéré et je vous tiens au courant.

Inutile de t'ennerver comme ca

[orafrance]

essaye de mettre des index sur :
p.code_commun
t.date_mep
(t.code_entrep,t.code_produit)

On te mache le boulot mais t'es pas fichu de tester convenablement en nous donnant les nouveaux plans d'exécution et aussi en répondant aux questions (je n'ai toujours pas l'assurance que tu as calculé les stats convenablement )

Merci donc de faire un minimum d'effort

[Kiroukool]

Envoyé par orafrance

essaye de mettre des index sur :
p.code_commun
t.date_mep
(t.code_entrep,t.code_produit)

On te mache le boulot mais t'es pas fichu de tester convenablement en nous donnant les nouveaux plans d'exécution et aussi en répondant aux questions (je n'ai toujours pas l'assurance que tu as calculé les stats convenablement )

Merci donc de faire un minimum d'effort

C'est pas ca mais je suis une quiche en base de données et je suis au boulot je fais d'autres choses. Alors je vais mettre les index que tu suggères et vous redonner le plan d'execution. Je n'ai pas fait de calculs de stats tout simplement car je ne sais pas


Voila j'ai retesté avec ces index et ca va deja plus vite sans le order by (environ une minute). Par contre avec le order by ca prend toujours beaucoup de temps.


Voici le plan d'execution :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 ------------------------------------------------------------------------------------------
| Id  | Operation                      |  Name                   | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         | 20110 |  1060K|  1477 |
|   1 |  SORT ORDER BY                 |                         | 20110 |  1060K|  1477 |
|   2 |   FILTER                       |                         |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID | TABLE_STOCKAGE      |    32 |  1184 |     2 |
|   4 |     NESTED LOOPS               |                         | 20110 |  1060K|  1260 |
|   5 |      TABLE ACCESS FULL         | QUANTITE                    |   629 | 10693 |     2 |
|   6 |      INDEX RANGE SCAN          | CODE_COMMUN_IND           |  7788 |       |     1 |
|   7 |    SORT AGGREGATE              |                         |     1 |    18 |       |
|   8 |     TABLE ACCESS BY INDEX ROWID| TABLE_STOCKAGE      |     1 |    18 |     2 |
|   9 |      INDEX RANGE SCAN          | TABLE_STOCKAGE_IND  |     1 |       |     1 |
------------------------------------------------------------------------------------------

[orafrance]

t'as fait l'index de code_commun sur la table quantité ?

Si c'est le cas, essaye ceci :

Code :

SELECT   /*+ index(p,p_index) */ t.code_entrep,

en remplaçant p_index par le nom de l'index. Peut-tu nous communiquer la valeur de paramètre SORT_AREA_SIZE et PGA_AGGREGATE_TARGET STP ?

[orafrance]

essaye ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT   t.code_entrep,
         p.code_qual,
         p.n_num_quantite,
	 t.date_mep
    FROM (
	     SELECT code_entrep, 
		        code_commun,
		        date_mep,
                MAX(t.date_mep) OVER (PARTITION BY t.code_entrep,t.code_produit) max_date 
		   FROM table_stockage
		  ) t JOIN quantite p ON (t.code_commun = p.code_commun)
   WHERE t.date_mep = t.max_date 
ORDER BY code_entrep;

si ça ne marche pas merci de nous donner l'ordre de création des tables ainsi que quelques lignes dans chacune d'elle pour tester la syntaxe

[Kiroukool]

Je n'ai pas les droits d'admin pour voir ces parametres...

quand a la requete sur le select index, elle ne fonctionne pas