"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

OK, a more directly equivalent example:

SQL> select sal, count(*), (sum(sal) over ())
2 from emp
3 group by sal;

SAL COUNT(*) (SUM(SAL)OVER())
---------- ---------- ----------------
800 1 24775
950 1 24775
1100 1 24775
1250 2 24775
1300 1 24775
1500 1 24775
1600 1 24775
2450 1 24775
2850 1 24775
2975 1 24775
3000 2 24775
5000 1 24775

12 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=36)
1 0 WINDOW (BUFFER)
2 1 SORT (GROUP BY) (Cost=54 Card=12 Bytes=36)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=42)




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
12 rows processed

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

Damn! Wish I knew how to "recall" a post! I just responded to this
with another wrong solution, but now I think I have the correct
solution. However, before this one appears you will see my 2nd wrong
solution and respond to that...

My mistake was treating the sum(sal) as if it operated on the base
table rather than on the result set after grouping. I believe this
finally fixes it and still only requires one scan of the table:

SQL> select sal, cnt, sum(sal*cnt) over () totsal
2 from
3 (
4 select sal, count(*) cnt
5 from emp
6 group by sal
7 );

SAL CNT TOTSAL
---------- ---------- ----------
800 1 29025
950 1 29025
1100 1 29025
1250 2 29025
1300 1 29025
1500 1 29025
1600 1 29025
2450 1 29025
2850 1 29025
2975 1 29025
3000 2 29025
5000 1 29025

12 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=312
)

1 0 WINDOW (BUFFER)
2 1 VIEW (Cost=54 Card=12 Bytes=312)
3 2 SORT (GROUP BY) (Cost=54 Card=12 Bytes=36)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=4
2)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
1 physical reads
0 redo size
629 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
12 rows processed

"Tony" <andrewst (AT) onetel (DOT) net.uk> wrote

You were fortunate that I showed some uncharacteristic restraint.


Thanks for clarifying that.