Discussion :

[GoLDoZ]

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 :

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

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 :

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

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 :

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

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

[GoLDoZ]

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

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

Code :

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

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

[Rams7s]

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 :

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

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

[Waldar]

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.

La table1 contient environ 4 millions de lignes, et la table2 en a 140 mille.

Code :

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

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.

[GoLDoZ]

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.

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

[Waldar]

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?

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

[mnitu]

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 ?