Ordre clause where

**Tay94** · 28/05/2009, 19h37

Bonjour,

J'ai un problème d'optimisation lors d'une requête : Oracle ne choisit pas la meilleure solution pour joindre mes tables.

Admettons la requête suivante pour simplifier :

Code :

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

1
2
3
4
select *
from TAB1, TAB2
where TAB1.nom=TAB2.nom
and fonction_tordue(TAB1.nom,TAB2.nom)>0.50

Ma fonction tordue est comme son nom l'indique : tordue donc lente.

Le problème que plutot de commencer par faire l'égalité sur le nom pour réduire le nombre de ligne et donc le nombre d'utilisation de cette fonction, il commence par fonction_tordue(TAB1.nom,TAB2.nom)>0.50

.

J'ai regardé le hint ORDERED_PREDICATES mais il s'utilise juste pour spécifier l'ordre des filtres (nom='DUPONT' ou REVENU > 600), il n'a pas d'impact sur les jointures (n° client = n° client...).

Auriez vous un conseil pour retravailler la requêtes ou le cas échéant un hint ?

Je me demande si procéder de la sorte a un impact (j'essaierai demain) :

Code :

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

1
2
3
4
select *
from TAB1, TAB2
where (TAB1.nom=TAB2.nom
and (fonction_tordue(TAB1.nom,TAB2.nom)>0.50))

Merci d'avance pour vos conseils.

**Waldar** · 28/05/2009, 19h50

Il suffit d'encapsuler votre jointure :

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
select
    <vos_colonnes>
from
    (
    select
        TAB1.nom as nom1,
        TAB2.nom as nom2,
        <vos_colonnes>
    from
        TAB1
        INNER JOIN TAB2
          ON TAB1.nom = TAB2.nom
    ) SR
where
    fonction_tordue(nom1, nom2) > 0.50

**Tay94** · 28/05/2009, 20h21

Bonjour,

Effectivement j'y avais pensé, mais comme ma requête est longue, je pensais qu'il existait une solution plus simple !

Ma requête est en fait de la forme :

Code :

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

1
2
3
4
5
6
7
8
select *
from 
(select *,fonction_tordue1(nom) as nom1
from TAB1) T1,
(select *,fonction_tordue1(nom) as nom2
from TAB2) T2
where T1.PRENOM=T2.PRENOM
and fonction_tordue2(nom1)=fonction_tordue2(nom2)

Donc il faudrait faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
select *
from 
(select * 
from
(select *,fonction_tordue1(nom) as nom1
from TAB1) T1,
(select *,fonction_tordue1(nom) as nom2
from TAB2) T2
where T1.PRENOM=T2.PRENOM)
where fonction_tordue2(nom1)=fonction_tordue2(nom2)

Merci pour l'aide, je testerai au boulot

!

**mnitu** · 29/05/2009, 10h41

Evitez le code de ce type

Code :

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

1
2
3
4
5
 
SELECT *
FROM TAB1, TAB2
WHERE (TAB1.nom=TAB2.nom
AND (fonction_tordue(TAB1.nom,TAB2.nom)>0.50))

il y a toutes les chances de plomber les performances avec cette solution, surtout quand la fonction est « tordu »
Changez de stratégie en utilisant les fonctions pipelined pour faire

Code :

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

1
2
 
select * from table(pipelined_fonction(arg1, arg2,...))

et intègrez le code de la fonction tordue dedans.

**Tay94** · 29/05/2009, 22h12

Bonjour,

Effectivement on passe de 1 heure à 2 minutes en faisant des sous requêtes dans la clause from.

Et merci pour ce conseil de fonction pipelined, bien que je préfère du sql optimisé parce que plus facilement maintenable !

Par contre y a t-il une différence entre :

Code sql :

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

1
2
3
4
5
6
7
select *
(select prenom, fonctionA(nom) as nom1
from TAB1) T1,
(select prenom, fonctionA(nom) as nom2
from TAB2) T2
where T1.prenom=T2.prenom
and nom1=nom2

Et

Code sql :

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

1
2
3
4
5
6
7
select *
(select prenom, nom
from TAB1) T1,
(select prenom, nom
from TAB2) T2
where T1.prenom=T2.prenom
and fonctionA(T1.nom)=fonctionA(T2.nom)

Je ne suis pas sur que dans le 1er cas Oracle va appliquer la fonctionA à chaque ligne des tables TAB1 et TAB2.

Les explain plan sont identiques, mais Oracle fait il vraiment la même chose ?

**NGasparotto** · 30/05/2009, 18h14

Envoyé par Tay94

...
Je ne suis pas sur que dans le 1er cas Oracle va appliquer la fonctionA à chaque ligne des tables TAB1 et TAB2.

Les explain plan sont identiques, mais Oracle fait il vraiment la même chose ?

Cas interessant, le plan d'execution est le meme en effet, mais le nombre d'appel a la fonction est moindre dans le second cas.
Voici un petit example :

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
SQL> create or replace function FonctionA(tbl varchar2, nom varchar2) return varchar2
  2  is
  3  begin
  4  dbms_output.put_line('call function for table '||tbl||' with variable : '||nom);
  5  return nom;
  6  end;
  7  /
 
Function created.
 
SQL> drop table t1
  2  /
 
Table dropped.
 
SQL> create table t1 as
  2  select distinct 0 object_id, object_name from user_objects where rownum <= 10
  3  /
 
Table created.
 
SQL> update t1 set object_id=rownum
  2  /
 
6 rows updated.
 
SQL> create unique index i1 on t1 (object_id)
  2  /
 
Index created.
 
SQL> drop table t2
  2  /
 
Table dropped.
 
SQL> create table t2 as
  2  select distinct 0 object_id, object_name from user_objects where rownum <= 15
  3  /
 
Table created.
 
SQL> update t2 set object_id=rownum
  2  /
 
9 rows updated.
 
SQL> create unique index i2 on t2 (object_id)
  2  /
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true)
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true)
 
PL/SQL procedure successfully completed.
 
SQL> set autot traceonly
SQL>
SQL> select *
  2  from  (select object_id, fonctionA('t1',object_name) nom1 from t1) t1,
  3        (select object_id, fonctionA('t2',object_name) nom2 from t2) t2
  4  where t1.object_id=t2.object_id
  5  and   nom1=nom2
  6  and rownum<=5;
 
call function for table t1 with variable : PS_AAP_ETHNIC_PMPT
call function for table t1 with variable : PS_AAP_YEAR
call function for table t1 with variable : PS_AAP_TBL
call function for table t1 with variable : PS0AAP_ETHNIC_PMPT
call function for table t1 with variable : PS_AAP_TBL_LNG
call function for table t1 with variable : PS0AAP_TBL_LNG
call function for table t2 with variable : PS_AAP_ETHNIC_PMPT
call function for table t1 with variable : PS_AAP_ETHNIC_PMPT
call function for table t2 with variable : PS_AAP_ETHNIC_PMPT
call function for table t2 with variable : PS_AAP_YEAR
call function for table t1 with variable : PS_AAP_YEAR
call function for table t2 with variable : PS_AAP_YEAR
call function for table t2 with variable : PS_AAP_TBL
call function for table t1 with variable : PS_AAP_TBL
call function for table t2 with variable : PS_AAP_TBL
call function for table t2 with variable : PS_AAP_YEAR_GOALS
call function for table t2 with variable : PS_AAP_YEAR_JG_GLS
call function for table t2 with variable : PS0AAP_ETHNIC_PMPT
call function for table t2 with variable : PS_AAP_TBL_LNG
call function for table t2 with variable : PS_AAP_YEAR_JOBGRP
call function for table t2 with variable : PS0AAP_TBL_LNG
 
Execution Plan
----------------------------------------------------------
Plan hash value: 808789222
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    37 |     7  (15)| 00:00:01 |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |
|*  2 |   HASH JOIN         |      |     1 |    37 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     6 |   108 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     9 |   171 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=5)
   2 - access("OBJECT_ID"="OBJECT_ID" AND
              "FONCTIONA"('t1',"OBJECT_NAME")="FONCTIONA"('t2',"OBJECT_NAME"))
 
 
Statistics
----------------------------------------------------------
         41  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        627  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
 
SQL>
SQL> select *
  2  from  (select object_id, object_name nom from t1) t1,
  3        (select object_id, object_name nom from t2) t2
  4  where t1.object_id=t2.object_id
  5  and   fonctionA('t1',t1.nom)=fonctionA('t2',t2.nom)
  6  and rownum<=5;
 
call function for table t1 with variable : PS_AAP_ETHNIC_PMPT
call function for table t1 with variable : PS_AAP_YEAR
call function for table t1 with variable : PS_AAP_TBL
call function for table t1 with variable : PS0AAP_ETHNIC_PMPT
call function for table t1 with variable : PS_AAP_TBL_LNG
call function for table t1 with variable : PS0AAP_TBL_LNG
call function for table t2 with variable : PS_AAP_ETHNIC_PMPT
call function for table t2 with variable : PS_AAP_YEAR
call function for table t2 with variable : PS_AAP_TBL
call function for table t2 with variable : PS_AAP_YEAR_GOALS
call function for table t2 with variable : PS_AAP_YEAR_JG_GLS
call function for table t2 with variable : PS0AAP_ETHNIC_PMPT
call function for table t2 with variable : PS_AAP_TBL_LNG
call function for table t2 with variable : PS_AAP_YEAR_JOBGRP
call function for table t2 with variable : PS0AAP_TBL_LNG
 
Execution Plan
----------------------------------------------------------
Plan hash value: 808789222
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    37 |     7  (15)| 00:00:01 |
|*  1 |  COUNT STOPKEY      |      |       |       |            |          |
|*  2 |   HASH JOIN         |      |     1 |    37 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |     6 |   108 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     9 |   171 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=5)
   2 - access("OBJECT_ID"="OBJECT_ID" AND
              "FONCTIONA"('t1',"OBJECT_NAME")="FONCTIONA"('t2',"OBJECT_NAME"))
 
 
Statistics
----------------------------------------------------------
         41  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        625  bytes sent via SQL*Net to client
        364  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
 
SQL>

Le deuxieme cas semble donc plus interressant.
La question restante sera : la valeur de nom1 est nom2 ne sera pas la meme dans la clause SELECT...

Nicolas.

**mnitu** · 30/05/2009, 18h17

Désolé,
Mais j'ai un peu du mal a saisir votre point de vue quand vous parlez de «*préférer du SQL optimisé*» après avoir utilise une fonction «*tordue*» écrite en PL/SQL très probablement, de la manière:

Code :

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

1
2
 
And f(t1.col) = f(t2.col)

**Tay94** · 03/06/2009, 23h13

Bonjour,

Mes essais me font penser que même si j'ai une requête de la forme suivante :

Code sql :

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

1
2
3
4
5
6
7
SELECT *
(SELECT prenom, fonctionA(nom) AS nom1
FROM TAB1) T1,
(SELECT prenom, fonctionA(nom) AS nom2
FROM TAB2) T2
WHERE T1.prenom=T2.prenom
AND nom1=nom2

Oracle ne calculera par forcément fonctionA pour chaque ligne des 2 tables.

J'ai une requête de la forme déjà en production, et 20 millions de lignes dans une des tables, or ma fonction met 5 secondes pour convertir 1000 lignes. Mais ma requête passe en 8 minutes.

Je pense que les statistiques sur les tables doivent jouer car vous avez fait des essais sur de petites tables.

Ma conclusion c'est que si on doit faire une jointure sur une fonction complexe il peut être préférable d'avoir une colonne avec le résultat de cette fonction.

Et aussi qu'Oracle ne suit pas forcément la logique de la requête (pour arriver au même résultat bien sur), ce qui est plus souvent un bien qu'un mal.

**mnitu** · 04/06/2009, 10h24

Petit tables ou gros tables peut importe.
Désolé mais, je ne partage pas votre point de vue. Il est vrai qu’Oracle peut être surprenant parfois : regardez la différence entre count(*) et count(fonc) au niveau des nombres d’appels à la fonction f dans l’exemple qui suit. D’autre il n’est pas du tout garanti que si votre fonction mets 5 seconde pour convertir 1000 lignes il prendra toujours 5 secondes pour chaque lot de 1000 lignes. Vous est en train d’extrapoler des résultats des mesures pour lesquels vous nous ne dits rien sur la façon que vous avez employez pour les faires.

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> Create Or Replace Function f Return Number Is
  2  Begin
  3    dbms_application_info.set_client_info( sys_context( 'userenv', 'client_info')+1 );
  4    return 1;
  5  End;
  6  /
 
Function created
 
SQL> exec dbms_application_info.set_client_info(0)
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> With generator As
  2  (Select rownum As id,
  3          rpad('x',50,'x') lib
  4    From all_objects
  5   Where rownum <= 10
  6  ),
  7  Data As
  8  (Select id, lib, f() As fonc
  9    From generator
 10  )
 11  Select count(*)
 12    From Data
 13  /
 
  COUNT(*)
----------
        10
 
SQL> 
SQL> select sys_context( 'userenv', 'client_info')
  2    from dual
  3  /
 
SYS_CONTEXT('USERENV','CLIENT_
--------------------------------------------------------------------------------
0
 
SQL> 
SQL> With generator As
  2  (Select rownum As id,
  3          rpad('x',50,'x') lib
  4    From all_objects
  5   Where rownum <= 10
  6  ),
  7  Data As
  8  (Select id, lib, f() As fonc
  9    From generator
 10  )
 11  Select count(fonc)
 12    From Data
 13  /
 
COUNT(FONC)
-----------
         10
 
SQL> 
SQL> select sys_context( 'userenv', 'client_info')
  2    from dual
  3  /
 
SYS_CONTEXT('USERENV','CLIENT_
--------------------------------------------------------------------------------
10
 
SQL> exec dbms_application_info.set_client_info(0)
 
PL/SQL procedure successfully completed
 
SQL> 
SQL> With generator As
  2  (Select rownum As id,
  3          rpad('x',50,'x') lib
  4    From all_objects
  5   Where rownum <= 10000
  6  ),
  7  Data As
  8  (Select id, lib, f() As fonc
  9    From generator
 10  )
 11  Select count(fonc)
 12    From Data
 13  /
 
 
COUNT(FONC)
-----------
      10000
 
SQL> 
SQL> select sys_context( 'userenv', 'client_info')
  2    from dual
  3  /
 
SYS_CONTEXT('USERENV','CLIENT_
--------------------------------------------------------------------------------
10000
 
SQL>

Ordre clause where

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager