Discussion :

[brolon]

Bonjour à tous,

une autre question me vient encore à l'esprit. Si j'effectue ceci :

Code :

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

1
2
3
SELECT *
FROM MA_TABLE
WHERE COLONNE_A > AVG(COLONNE_A)

Le Where doit être évalué pour chaque record de ma table, mais le AVG sera-t-il aussi recalculé autant de fois ? Ou bien, Oracle est plus malin que ça ?

[Waldar]

Syntaxiquement, ça ne passera pas car vous ne pouvez pas utiliser d'aggrégat dans le where.

La requête suivante sera correcte :

Code :

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

1
2
3
SELECT *
FROM MA_TABLE
WHERE COLONNE_A > (SELECT AVG(COLONNE_A) FROM MA_TABLE)

La sous-requête ne sera bien évaluée qu'une seule fois.

[laurentschneider]

Code :

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

1
2
3
4
5
 
select * from 
(SELECT t.*, AVG(COLONNE_A) over () AVG_A
FROM MA_TABLE)
WHERE COLONNE_A > AVG_A

ainsi la table MA_TABLE ne sera scannée qu'une fois !

[brolon]

Laurent, merci pour ta réponse éclairée

Avec la solution proposée par Waldar, la table sera scannée plus d'une fois ?

[Waldar]

Oui deux fois, une fois pour calculer la moyenne globale et une fois pour comparer celle-ci ligne à ligne.

C'est l'avantage des fonctions analytiques !

[laurentschneider]

oui, mais il faut toujours tester... parfois les résultats peuvent décevoir aussi

Code :

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

1
2
3
4
5
6
7
 
create table lsc_t1(x number primary key, a number, b varchar2(4000));
insert into lsc_t1 select rownum,1,'xxxxxxxxxxx' from all_objects;
insert into lsc_t1 values(0,10000,'yyy');
exec dbms_stats.gather_table_stats(USER,'LSC_T1');
select x,a from (select t.*, avg(a) over () avg_a from lsc_t1 t) where a>avg_a;
select x,a from lsc_t1 where a>(select avg(a) from lsc_t1);

Code :

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

1
2
3
4
5
6
7
8
9
10
11
select x,a from (select t.*, avg(a) over () avg_a from lsc_t1 t) 
where a>avg_a;
         X          A
---------- ----------
         0      10000
Elapsed: 00:00:00.20
select x,a from lsc_t1 where a>(select avg(a) from lsc_t1);
         X          A
---------- ----------
         0      10000
Elapsed: 00:00:00.07

bonne soirée

[brolon]

Je n'ai pas encore le réflexe du test ... merci pour cet exemple