SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on the order of evaluation when multiple rules are defined.

SEQUENTIAL ORDER

By default, Oracle evaluates rules in the order they appear in the RULES clause. This is know as SEQUENTIAL ORDER. For example, consider the following query.

select
  key, m1, m2, m3
from
  t
where
  key <= 3
model
  ignore nav
  dimension by ( key )
  measures     ( 0 as m1, 0 as m2, 0 as m3 )
  rules
    update
    SEQUENTIAL ORDER  -- this line optional, SEQUENTIAL ORDER is the default
  (
    m1[any] = m2[ cv() + 1 ] ,  -- rule 1
    m2[any] = cv(key)        ,  -- rule 2
    m3[any] = m2[ cv() + 1 ]    -- rule 3
  )
order by
  key
;
 

   KEY         M1         M2         M3
------ ---------- ---------- ----------
     1          0          1          2
     2          0          2          3
     3          0          3          0
 

The results show that processing occured in this fashion

1. Rule 1 is applied to cell m1[1]
2. Rule 1 is applied to cell m1[2]
3. Rule 1 is applied to cell m1[3]
4. Rule 2 is applied to cell m2[1]
5. Rule 2 is applied to cell m2[2]
6. Rule 2 is applied to cell m2[3]
7. Rule 3 is applied to cell m3[1]
8. Rule 3 is applied to cell m3[2]
9. Rule 3 is applied to cell m3[3]

not this fashion.

1. Rule 1 is applied to cell m1[1]
2. Rule 2 is applied to cell m2[1]
3. Rule 3 is applied to cell m3[1]
4. Rule 1 is applied to cell m1[2]
5. Rule 2 is applied to cell m2[2]
6. Rule 3 is applied to cell m3[2]
7. Rule 1 is applied to cell m1[3]
8. Rule 2 is applied to cell m2[3]
9. Rule 3 is applied to cell m3[3]

If you need the second behaviour, you can use SEQUENTIAL ORDER with the ITERATE feature described in the ITERATE tutorial.

select
  key, m1, m2, m3
from
  t
where
  key <= 3
model
  ignore nav
  dimension by ( key )
  measures     ( 0 as m1, 0 as m2, 0 as m3 )
  rules
    update
    SEQUENTIAL ORDER
    ITERATE (3)
  (
    m1[ITERATION_NUMBER+1] = m2[ cv() + 1 ] ,  -- rule 1
    m2[ITERATION_NUMBER+1] = cv(key)        ,  -- rule 2
    m3[ITERATION_NUMBER+1] = m2[ cv() + 1 ]    -- rule 3
  )
order by
  key
;
 

   KEY         M1         M2         M3
------ ---------- ---------- ----------
     1          0          1          0
     2          0          2          0
     3          0          3          0
 

AUTOMATIC ORDER

In addition to SEQUENTIAL ORDER there is another rule ordering behaviour available. It is called AUTOMATIC ORDER. With AUTOMATIC ORDER Oracle determines rule evaluation order based on the dependencies between rules. Consider the following example.

select
  key, m1, m2, m3
from
  t
where
  key <= 3
model
  ignore nav
  dimension by ( key )
  measures     ( 0 as m1, 0 as m2, 0 as m3 )
  rules
    update
    AUTOMATIC ORDER
  (
    m1[any] = m2[ cv() + 1 ] ,  -- rule 1
    m2[any] = cv(key)        ,  -- rule 2
    m3[any] = m2[ cv() + 1 ]    -- rule 3
  )
order by
  key
;
 

   KEY         M1         M2         M3
------ ---------- ---------- ----------
     1          2          1          2
     2          3          2          3
     3          0          3          0
 

Unlike SEQUENTIAL ORDER, these results show that AUTOMATIC ORDER caused Oracle to evaluate rule 2 before rules 1 and 3 because rules 1 and 3 depend on the measure modified by rule 2.

Multiple Cell Assignments

With SEQUENTIAL ORDER the same cell can be assigned a value many different times.

select
  key, m1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m1 )
  rules SEQUENTIAL ORDER
  (
    M1[0] = 10 ,
    M1[0] = 20 ,
    M1[0] = 30
  )
;
 

   KEY         M1
------ ----------
     0         30
 

With AUTOMATIC ORDER you can assign a value to a cell only once. Attempting to do otherwise will produce an error.

select
  key, m1
from
  dual
model
  dimension by ( 0 as key )
  measures     ( 0 as m1 )
  rules AUTOMATIC ORDER
  (
    M1[0] = 10 ,
    M1[0] = 20 ,
    M1[0] = 30
  )
;
    M1[0] = 20 ,
    *
ERROR at line 11:
ORA-32630: multiple assignment in automatic order MODEL


 

Gotchas

You cannot use ITERATE with AUTOMATIC ORDER.

select
  key, m1, m2, m3
from
  t
where
  key <= 3
model
  dimension by ( key )
  measures     ( 0 as m1, 0 as m2, 0 as m3 )
  rules
    AUTOMATIC ORDER
    ITERATE (3)
  (
    m1[iteration_number+1] = sum( m2 )[any] ,  -- rule 1
    m2[iteration_number+1] = 1              ,  -- rule 2
    m3[iteration_number+1] = sum( m2 )[any]    -- rule 3
  )
order by
  key
;
    ITERATE (3)
    *
ERROR at line 12:
ORA-32607: invalid ITERATE value in MODEL clause