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

- Rule 1 is applied to cell m1[1]
- Rule 1 is applied to cell m1[2]
- Rule 1 is applied to cell m1[3]
- Rule 2 is applied to cell m2[1]
- Rule 2 is applied to cell m2[2]
- Rule 2 is applied to cell m2[3]
- Rule 3 is applied to cell m3[1]
- Rule 3 is applied to cell m3[2]
- Rule 3 is applied to cell m3[3]

not this fashion.

- Rule 1 is applied to cell m1[1]
- Rule 2 is applied to cell m2[1]
- Rule 3 is applied to cell m3[1]
- Rule 1 is applied to cell m1[2]
- Rule 2 is applied to cell m2[2]
- Rule 3 is applied to cell m3[2]
- Rule 1 is applied to cell m1[3]
- Rule 2 is applied to cell m2[3]
- 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