AVG + SELECT sur une colonne ?

Bonjour à tous,

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

Code:

---

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

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

La requête suivante sera correcte :

Code:

---

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.

Code:

---

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 !

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

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

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 !

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

Code:

---

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:

---

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

---

:king: bonne soirée

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