Optimisation d'un Group by

Bonjour.:)

J'ai une requête trop longue à exécuter et je ne sais pas comment l'améliorer, notamment s'il y a des index précis à utiliser dans le cadre d'un group
by.

La requête est du type:
Code:

1 2 3 4 5 select a.col1, a.col2, a.col3, decode (b.col1,..<valeurs en dur>....) as bcol, sum(a.col4), sum(a.col5) from table1 a, table2 b where a.col6 between b.valmin and b.valmax group by a.col1, a.col2, a.col3, decode (b.col1,..<valeurs en dur>....)
La table1 contient environ 4 millions de lignes, et la table2 en a 140 mille.

Le plan d'exécution est le suivant:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT STATEMENT 410339 HASH GROUP BY 410339 MERGE JOIN 258493 SORT JOIN 881 REMOTE TABLE2 168 FILTER Prédicats de filtre B.VALMAX>=TO_NUMBER(A.col6) SORT JOIN 35772 Prédicats d accès B.VALMIN<=TO_NUMBER(A.col6) Prédicats de filtre B.VALMIN<=TO_NUMBER(A.col6) TABLE ACCESS TABLE1 FULL 5636
La table1 a un index sur col6, la table2 a un index commun sur valmin, valmax.

Avant de commencer la réflexion, je vous donne une précision. J'utilise une astuce dans une autre requête (sur la même table), qui permet d'optimiser énormément le temps de traitement.
En l'occurrence, la requête suivante est très rapide:
Code:

1 2 3 4 5 6 7 select /*+ ordered use_nl(b) */ a.col1, a.col2, a.col3, decode (b.col1,..<valeurs en dur>....) as bcol, sum(a.col4), sum(a.col5) from table1 a, table2 b where a.col6 between b.valmin and b.valmax and b.valmax = (select min(b2.valmax) from table 2 b2 where b2.valmax >= a.col6)
Si j'enlève le Hint et la sous-requête en gras, ça n'en finit pas.

Je les ai ajoutés dans la requête avec le group by, mais je n'ai pas l'optimisation souhaitée...

Merci de m'aider à avancer:ccool:

Le we passé, je vous propose une simplification du problème. :D

Quel(s) index ajouter pour optimiser le group by suivant?
Code:

1 2 3 SELECT a.col1, a.col2, a.col3, count(1) as col4, sum(a.col5) FROM table1 a GROUP BY a.col1, a.col2, a.col3

Attention !

Le week-end passe, je vous propose de bien relire vos requetes de vore premier message, elle ne sont pas equivalentes!

La ou la 1ere va selectionner un tuple de la table1 autant de fois qu'il rentre dans un interval, la seconde requete va le selectionner autant de fois qu'il trouve le valmax.

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
drop table tmp2;
drop table tmp;
create table tmp(n number,to_add number);
create table tmp2(valmin number,valmax number);
 
insert into tmp2 values (10,12);
insert into tmp2 values (15,25);
insert into tmp2 values (15,27);
insert into tmp2 values (20,22);
insert into tmp2 values (10,22);
 
insert into tmp values (21,1);
insert into tmp values (11,-1);
 
select * from tmp,tmp2
  where n between valmin and valmax;
 
select * from tmp,tmp2 b
  where n between valmin and valmax
  and valmax=(select min(b2.valmax) from tmp2 b2
               where b2.valmax>=n);
 
select n,sum(to_add) from tmp,tmp2
  where n between valmin and valmax group by n;
 
select n,sum(to_add) from tmp,tmp2 b
  where n between valmin and valmax
  and valmax=(select min(b2.valmax) from tmp2 b2
               where b2.valmax>=n) group by n;

Donc, dans le premier cas pour n=21, on le retrouve pour 4tuples de tmp2, et dans le second cas pour tous les tuples qui ont pour valmax=(la plus proche valeur au dessus de 21)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
TMP@MINILHC >select n,sum(to_add) from tmp,tmp2
  2    where n between valmin and valmax group by n;
 
         N SUM(TO_ADD)
---------- -----------
        11          -2
        21           4
 
Elapsed: 00:00:00.00
TMP@MINILHC >
TMP@MINILHC >select n,sum(to_add) from tmp,tmp2 b
  2    where n between valmin and valmax
  3    and valmax=(select min(b2.valmax) from tmp2 b2
  4                 where b2.valmax>=n) group by n;
 
         N SUM(TO_ADD)
---------- -----------
        11          -1
        21           2

Avant de mettre un index, il va falloir savoir quel est le resultat que vous cherchez a obtenir :mrgreen:

Citation:

Envoyé par GoLDoZ

Quel(s) index ajouter pour optimiser le group by suivant ?

Soit aucun, soit (col1, col2, col3, col5).
Mais effectivement ça n'a rien à voir avec votre premier problème.
Citation:
La table1 contient environ 4 millions de lignes, et la table2 en a 140 mille.
Code:

1 2 3 4 5 6 7 8 FILTER Prédicats de filtre B.VALMAX>=TO_NUMBER(A.col6) SORT JOIN 35772 Prédicats d accès B.VALMIN<=TO_NUMBER(A.col6) Prédicats de filtre B.VALMIN<=TO_NUMBER(A.col6)
Déjà évitez la conversion implicite, surtout quand c'est sur la table qui a la plus forte volumétrie.

Merci pour ces réponses.

@Rams7s: j'ai oublié de préciser une chose essentielle: les intervalles de la table2 sont strictement disjoints. La valeur recherchée est au plus dans un intervalle. Sans cette condition, je suis bien d'accord qu'il n'y a pas d'équivalence entre les requêtes...
Désolé pour avoir oublié cette précision. :oops:

@waldar: c'est ce que je pensais, merci pour la confirmation. :)

Je vais essayer de dissocier la recherche de la valeur dans l'intervalle de la table2, et le regroupement des données, car j'ai l'impression que l'optimisation du Hint ne fonctionne pas avec le group by. Je vais faire une table temporaire.
Du coup, le Group By se fera sur 7 millions de lignes et non 4 millions. D'où ma recherche sur les index et la requête proposée ce matin.

Premier pas, on va probablement mettre un index sur col6 dans la table1, sachant qu'il y a déjà un index sur le couple (valmin, valmax) de la table2.

Edit: @Waldar: concernant la conversion implicite, je n'ai pas trop le choix, j'ai un VARCHAR2(19) d'un côté (souvent de longueur 19), un NUMBER de l'autre. Pour optimiser, je dois les mettre au même format, non? :idea:

Citation:

Envoyé par GoLDoZ

Edit: @Waldar: concernant la conversion implicite, je n'ai pas trop le choix, j'ai un VARCHAR2(19) d'un côté (souvent de longueur 19), un NUMBER de l'autre. Pour optimiser, je dois les mettre au même format, non? :idea:

Vous convertissez 4 millions de VARCHAR en nombre alors que vous pourriez convertir 140.000 nombres en VARCHAR.

Vous ne pouvez pas optimiser ce que vous ne comprenez pas. Suivez les étapes indiquées dans cet fil de discussion.
Avez-vous remarqué la présence du mot clé REMOTE dans votre plan ?

@waldar: effectivement...

@mnitu: pour tout dire, la table d'insertion est dans le schéma 1 de la base 1, table1 est dans le schéma 2 de la base 1, et table2 est sur le schéma 1 de la base 2. :aie:
Je fais des tests sur des tables toutes dans le même schéma, pouvant recopier les données avant de les traiter. Je vais mettre le bon explain plan depuis les recopies.

Edit: @Waldar: je ne peux faire un to_char car l'index sur (valmin, valmax) n'est plus utilisé et la requête est sans fin, alors qu'elle est très rapide sinon.

Jetez un coup d'oeil sur la partie 9 et la partie 10

http://sqlpro.developpez.com/cours/optimiser/

Bonjour.

J'ai résolu le problème en faisant 3 étapes pour arriver à mes fins. Le tout prend 5 minutes. J'ai visé trop haut en voulant tout faire d'un coup car je voulais éviter de créer des tables temporaires. Mais c'est impossible. En effet, la table1 a environ 7 millions de lignes par partition et... compte 81 colonnes. :aie: Et mon hint ne fonctionne pas avec un dblink.

En isolant les colonnes nécessaires dans une table temporaire dans le bon schéma, en recopiant la table2 dans la bonne base, je peux utiliser mon hint, et après faire le regroupement. Ce dernier fonctionne terriblement bien avec l'index sur les 5 colonnes (11s pour regrouper les 7 millions en 240).

Merci à tous. :ccool: