Utilisation de minus: optimisation de la requête

**claralavraie** · 20/04/2006, 17h46

Bonjour,

j'ai une requête qui dure 1h30 environ et j'aimerais pouvoir réduire ce temps.
J'utilise MINUS et je me demandais si ce n'était pas du à cela.
Je ne m'y connais pas trop en optimisation.
Si quelqu'un pouvait m'aider, ce serait très sympa.
Voici ma requête:

Code :

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

1
2
3
4
5
6
7
8
9
 
select count(colonne1) 
from(select colonne2 
from table1, table2, table3 
where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
minusselect colonne5 from table4)

il y a un index sur les identifiant des table 1 2 et 3.
Merci de votre aide...

**nuke_y** · 20/04/2006, 18h28

Est-ce qu'il y a un index sur colonne5 de la table 4 ?

Et que donne ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 select count(1)from (
(
select colonne2 
from table1, table2, table3, table4
where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
and colonne2=colonne5(+)
and colonne5 is null
)
)

Car si j'ai bien compris ta requête, tu souhaites avoir une jointure entre Table1, Table 2 et Table 3 et retirer de ce résultat toutes les lignes qui ont un colonne2 présent dans le champ Colonne5 de Table4 c'est ça ?

**plabrevo** · 20/04/2006, 18h38

Il est assez vain en general de faire des travaux d'optimisation de requete sans disposer de

la volumetrie
de la liste des indexes et de leur selectivite.
accessoirement, de l'explain plan.

En ce qui concerne le minus, il est probable que le temps de response sans l'operation MINUS doive approcher les 1h29 minutes.

**orafrance** · 21/04/2006, 09h05

Et pourquoi pas simplement :

Code :

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

1
2
3
4
5
6
 select count(colonne2)
from table1, table2, table3 , table4
where valeur1<>'333'and valeur2=' 'and valeur3like'x%'and valeur4='Y' 
and idtable1 = idtable2 and idtable2 = idtable3 
and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
and colonne2 <> colonne5

**claralavraie** · 21/04/2006, 09h45

Merci pour tous vos conceils.
je vais essayer ce que vous m'avez dit et je vous tiens au courant.

**claralavraie** · 21/04/2006, 10h05

J'ai bien un index sur ma colonne5 de ma table 4.
Pour ce qui est des requêtes, je les ai essayé toutes les 2 mais elle ne fonctionne pas.
Merci quand même de votre aide.

**orafrance** · 21/04/2006, 10h25

qu'est ce qui ne fonctionne pas ?

Essaye ça sinon :

Code :

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

1
2
3
4
5
6
7
select count(colonne1) 
from(select colonne2 
from table1, table2, table3 
where valeur1<>'333'and valeur2=' 'and valeur3 like'x%'and valeur4='Y' 
and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' 
and 
colonne2 NOT IN (select colonne5 from table4))

Avec un index sur colonne 2

Par ailleurs tu fais count(colonne1) alors que c'est colonne2 que tu sélectionnes... je vois mal comment ça pourrait fonctionner

**nuke_y** · 21/04/2006, 10h31

Euh attends, elles ne fonctionnent pas, c'est à dire ? Il se passe quoi ?

Fred_D je ne suis pas d'accord avec ta PREMIERE requête. Pour moi le but c'est de retirer à la jointure les lignes pour lesquelles la valeur de colonne2 existe dans colonne5 de table4.

Donc :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
SQL> create table test1( ID number);
 
Table crÚÚe.
 
SQL> insert into test1 values(1);
 
1 ligne crÚÚe.
 
SQL> insert into test1 values(2);
 
1 ligne crÚÚe.
 
SQL> insert into test1 values(3);
 
1 ligne crÚÚe.
 
SQL> insert into test1 values(4);
 
1 ligne crÚÚe.
 
SQL> insert into test1 values(5);
 
1 ligne crÚÚe.
 
 
SQL> create table test2( ID number);
 
Table crÚÚe.
 
SQL> insert into test2 values(3);
 
1 ligne crÚÚe.
 
SQL> insert into test2 values(5);
 
1 ligne crÚÚe.
 
 
SQL> select t1.* from test1 t1, test2 t2 where t1.id<>t2.id;
 
        ID
----------
         1
         2
         4
         5
         1
         2
         3
         4
 
8 ligne(s) sÚlectionnÚe(s).
 
SQL> select t1.* from test1 t1 where not exists (select 1 from test2 t2
where t2.id=t1.id);
 
        ID
----------
         1
         2
         4
 
SQL> select t1.* from test1 t1, test2 t2 where t1.id=t2.id(+) and t2.id
is null;
 
        ID
----------
         1
         2
         4

**nuke_y** · 21/04/2006, 10h35

Code :

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

1
2
3
4
5
6
7
8
9
SQL> select t1.* from dwh.test1 t1 where t1.id not IN (Select t2.id from dwh.test2 t2);
 
        ID
----------
         1
         2
         4
 
---

Ok avec ta DEUXIEME requête. On gagne en perf avec le NOT IN plutôt que le EXISTS ? J'imagine que oui si le Sous-Select ramène peu de lignes.

**claralavraie** · 21/04/2006, 10h35

Envoyé par Fred_D

qu'est ce qui ne fonctionne pas ?

je n'obtiens pas les mêmes résultats.
je vais essayer de voir pourquoi.
Sinon j'ai un index sur ma colonne 2 et en effet ma colonne 1 et 2 sont les mêmes. Je me suis trompée en écrivant

.

Sinon pour le not in, j'ai essayé mais ma reqûete est aussi longue.

**nuke_y** · 21/04/2006, 10h36

Est-ce que tu peux nous confirmer que ton but est bien de soustraire à ta liste les lignes pour lesquelles la valeur de colonne2 existe dans colonne4 ?

**claralavraie** · 21/04/2006, 10h37

Envoyé par nuke_y

Est-ce que tu peux nous confirmer que ton but est bien de soustraire à ta liste les lignes pour lesquelles la valeur de colonne2 existe dans colonne4 ?

Oui c'est ce que je veux faire. et J'aimerais avoir une requête la moins longue possible.

**nuke_y** · 21/04/2006, 10h41

Et combien de temps te prend cette requête ?

Code :

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

1
2
3
4
5
6
7
 select count(colonne2) 
from(select colonne2 
from table1, table2, table3 
where valeur1<>'333'and valeur2=' 'and valeur3 like'x%'and valeur4='Y' 
and idtable1 = idtable2 and idtable2 = idtable3 and substr(valeur5,length(ivaleur5)-2)= 'valeur' )
 
--

Pour être sûr que ce n'est pas la requête principale qu'il faut améliorer.

**claralavraie** · 21/04/2006, 10h50

Elle dure 10 secondes avec le minus et 7 secondes sans le minus.

Mais sachant que cette requête, je l'effectue pour 301 valeur et pour chacune de ces valeurs, je l'a fait 3 fois avec un minus différent.
Donc ça fait très long.
Je ne sais pas si je peux faire plus court.

**orafrance** · 21/04/2006, 10h58

dans ce cas, il faut se pencher sur le GROUP BY pour éviter de la lancer 300 fois

**claralavraie** · 21/04/2006, 11h19

Mais je ne vois pas le faire avec un GROUP BY.
J'ai créé une procédure qui fait ces requêtes pour les différentes valeurs et l'insère dans une table.
je voudrais optimiser cette requête pour la rendre plus rapide.
Voici ma procédure en entier:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
CREATEORREPLACEPROCEDURE remplir_table IS
 
code_action varchar2(20);
libelle_action varchar2(30); 
le_sql VARCHAR2(5000);
nom_extract varchar2(30);
nombre number;
BEGIN
executeimmediate'TRUNCATE TABLE COUNT;
FOR c IN(select decode(length(valeur),1,'00',2,'0')||to_char(valeur) mag fromtable_mag)
LOOP

 
BEGIN
select code, libelle into code_action, libelle_action from table_code wherecocod='S';
select colonne5 into nom from table5where exnum ='REFUSS';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 like ''x%''and valeur4=''Y'' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
COMMIT;
END;

-- insertion des comptages pour les mails
BEGIN
select code, libelle into code_action, libelle_action from table_code wherecocod='W';
select colonne5 into nom from table5wherewhere exnum ='REFUSEM';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and valeur3 <>'' '' and valeur4 IN(''Y'', ''A'', ''T'') and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(val)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
COMMIT;
END;

-- insertion des comptages pour les couriers
BEGIN
 
select code, libelle into code_action, libelle_action from table_code wherecocod='M';
select colonne5 into nom from table5wherewhere exnum ='REFUSEA';
le_sql:='select count(colonne1) from (select colonne1 from table1, table2, table3 where valeur1<>'333' and valeur2= ' ' and idtable1 = idtable2 and idtable1 = idtable3 and substr(idagr,length(ival)-2)= :1 minus select valeur5 from '||nom||')';
executeimmediate le_sql into nombre using c.mag;
insertintoCOUNT(mag, action, libelle, count)values(c.mag, code_action,libelle_action, nombre); 
COMMIT;
END;
ENDLOOP;
Exception
WhenOTHERSthennull;
END;
/

donc voilà je ne sais pas si je peux optimiser les requêtes pour que le temps d'execution de cette procédure soit plus rpide.