With certain MODEL queries it may be tempting to add a measure and then use it to hold intermediate calculations in every row of the result set. The final result is then extracted from a single row. This approach can consume a lot of session memory (see Rows). Approaches that update fewer rows and use fewer measures can work better.
For example, Query 1 below uses two measures and updates 100,000 rows. Query 2 uses a single measure and only updates a single row. Query 3 uses a single measure and updates a single row as well, but it consumes even less memory than query 2 because it uses a reference model (see Reference Models).
Query 1:
select max(total) as total from
( select total from t_100k
model ignore nav
dimension by ( key)
measures ( key as amount, 0 as total )
rules ( total[any] order by key = total[cv()-1] + amount[cv()] )
)
;
TOTAL
----------
4999950000
Query 2:
select total from t_100k
model
ignore nav
return updated rows
dimension by ( rownum - 1 as row_num )
measures ( key as total )
rules
iterate(1000000) until ( presentv( total[iteration_number+1],1,0 ) = 0 )
( total[-1] = total[-1] + total[iteration_number] )
;
TOTAL
----------
4999950000
Query 3:
select total from dual
model
ignore nav
reference r on
( select rownum - 1 as row_num, key as amount from t_100K )
dimension by ( row_num )
measures ( amount )
main m
dimension by ( 0 as key )
measures ( 0 as total )
rules
iterate(1000000) until ( presentv( r.amount[iteration_number+1],1,0 ) = 0 )
( m.total[0] = m.total[0] + r.amount[iteration_number] )
;
TOTAL
----------
4999950000
Metric Query 1 Query 2 Query 3
-------------------------- ------- ------- -------
session pga memory max (K) 8,448 4,672 3,008
session uga memory max (K) 8,362 4,605 2,926
See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
Of course, if we were working on queries like the ones above and did not realize they could all be replaced with this query ...
select sum( key ) from t_100k ;
SUM(KEY) ---------- 4999950000
then perhaps a good vacation would provide greater performance improvements than any of the tuning tips given here. ;-)
Warning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and ave rage the results from multiple runs before making performance decisions.