SQL Snippets ™: Tutorials for Oracle Developers

Operations implemented with MEASURES use less memory than those implemented with RULES.

For example, Query 2 below will use less memory than Query 1.

Query 1:

select m_1, m_3 from t_1k
model
  dimension by (key)
  measures     ( m_1, m_2, m_1 as m_3 )
  RULES SEQUENTIAL ORDER
  (
    M_3[ANY] = M_1[CV()] || M_2[CV()] ,
    m_1[any] = 'x'
  )
;

Test Complete
 

Query 2:

select m_1, m_3 from t_1k
model
  dimension by (key)
  MEASURES
  (
    m_1,
    M_1 || M_2 AS M_3
  )
  rules ( m_1[any] = 'x' )
;

Test Complete
 

Results

Metric                     Query 1 Query 2
-------------------------- ------- -------
session pga memory max (K) 7,488   3,392
session uga memory max (K) 7,295   3,318

See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
 

Gotchas

Be careful not to alter the end result when you move logic from rules to measures. For example, if we started with something similar to Query 1

select m_1, m_2, m_3 from t_1k where key < 10
model
  dimension by (key)
  measures     ( m_1, m_2, m_1 as m_3 )
  RULES SEQUENTIAL ORDER
  (
    m_1[any] = 'x' ,
    M_3[ANY] = M_1[CV()] || M_2[CV()] -- appears as second rule this time
  )
;
 

M_1 M_2 M_3
--- --- ---
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
x   b   xb
 

we would not get the same results by rewriting it like this.

select m_1, m_2, m_3 from t_1k where key < 10
model
  dimension by (key)
  MEASURES
  (
    m_1,
    m_2,
    m_1 || m_2 as m_3
  )
  rules ( m_1[any] = 'x' )
;
 

M_1 M_2 M_3
--- --- ---
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
x   b   ab
 

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.