select c1, c2, max(c3) .....
the sort group by will create a sorted list of c1, c2 as we scan the table - having c1, c2 being "unique" - and maintaining the max(c3).
So, if there are 100,000 unique c1, c2's - and there are 100,000,000 rows in the table - then there will be 100,000 c1/c2's in 'temp' whilst we find the max(c3) for each one.
I don't understand why you would compare using row_number() on the fly to creating a "skinny table" using group by???
Why wouldn't you compare using row_number to using group by in the fly?
And then compare "doing it on the fly (using either technique) versus using a materialized view"
The sort group by is there because - you asked for an AGGREGATE and that is how they are built.
hash group by does the same thing conceptually as a sort group by - but uses hashing to find the c1/c2 values instead of a sorted list of values.
Think of it this way, you have data in your table like this:
c1 c2 c3
----- ----- ------
1 1 2
1 2 3
2 1 4
1 1 42
......
we start scanning the data, we hit row 1, in temp we'll have:
1,1 -> 2
we hit row 2, now we have
1,1 -> 2
1,2 -> 3
we hit row 3, now we have
1,1 -> 2
1,2 -> 3
2,1 -> 4
we hit row 4, now we have:
1,1 -> 42
1,2 -> 3
2,1 -> 4
now return the data, you get the data as it appears right above, accidentally sorted in binary sort order (sort group by)
we have a sorted list - so we can lookup c1/c2 and perform the aggregation.
If you used hash group by it would be conceptually like the following:
hit row 1, hash(1,1) into a hash table slot in temp. Suppose that returns 1032 as the 'slot' in the hash table. We put (1,1->2) into slot 1032 in temp.
hit row 2, hash(1,2) into that hash table. Suppose that returns 55 as the slot. We put (1,2->3) into slot 55.
hit row 3, hash(2,1) into that hash table. Suppose that returns 201 as the slot. We put (2,1->4) into slot 201.
hit row 4, hash(1,1) - you get 1032 again. Goto slot 1032 - see data is there, modify it (do the aggregate) and store (1,1->42) in slot 1032.
When done with all rows in table, return the data from the hash table, you get
1,2->3
2,1->4
1,1->42
as the output (not sorted in a binary fashion, hashing spreads data out) - hash group by.
You can see that the amount of "temp space" needed is a function of the rows returned (the distinct cardinality of c1,c2 - NOT the number of rows in the table) via the following:
ops$tkyte%ORA9IR2> /*
DOC>drop table t1 ;
DOC>drop table t2 ;
DOC>
DOC>create table t1
DOC>as
DOC>select mod(rownum,2) c1, mod(rownum,2)+1 c2, object_id c3
DOC> from all_objects
DOC>/
DOC>create table t2
DOC>as
DOC>select mod(rownum,500) c1, mod(rownum,200)+1 c2, object_id c3
DOC> from all_objects
DOC>/
DOC>*/
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter session set workarea_size_policy = manual;
Session altered.
ops$tkyte%ORA9IR2> alter session set sort_area_size = 65536;
Session altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> set autotrace traceonly
ops$tkyte%ORA9IR2> select c1, c2, max(c3) from t1 group by c1, c2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
62 consistent gets
0 physical reads
0 redo size
555 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
ops$tkyte%ORA9IR2> select c1, c2, max(c3) from t2 group by c1, c2;
1000 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T2'
Statistics
----------------------------------------------------------
0 recursive calls
57 db block gets
69 consistent gets
225 physical reads
0 redo size
23891 bytes sent via SQL*Net to client
1225 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1000 rows processed
ops$tkyte%ORA9IR2> set autotrace off
notice that when c1,c2 have few distinct values (2 in my example) - the "sort" (which is not really a sort, just a sorted set of distinct values of c1,c2) is done in memory (used a 64k sort area size).
when c1, c2 have many distinct values (1000 in my example) the "sort" (which is not a sort really) is swapped to disk - 64k could not hold 1,000 intermediate results.