In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on rule iteration.

To repetetively execute rules use the ITERATE feature. For example, to repeat a rule exactly five times you can use ITERATE like this.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( cast( 'm_1: ' as varchar2(20) ) as m_1 ) rules ITERATE (5) ( m_1[0] = m_1[0] || 'x' ) ;

KEY M_1 ------ -------------------- 0 m_1: xxxxx

The maximum number of iterations allowed (as tested in Oracle 10g XE) is 4,294,967,295 (i.e. 4Gig - 1).

If you specify more than one rule, let's use three as an example, processing proceeds as follows

- Rule 1 is executed in the first iteration
- Rule 2 is executed in the first iteration
- Rule 3 is executed in the first iteration
- Rule 1 is executed in the second iteration
- Rule 2 is executed in the second iteration
- Rule 3 is executed in the second iteration
- ...
- Rule 1 is executed in the X'th iteration
- Rule 2 is executed in the X'th iteration
- Rule 3 is executed in the X'th iteration

not

- Rule 1 is executed X times
- Rule 2 is executed X times
- Rule 3 is executed X times

The following example demonstrates rule iteration order.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( cast( null as varchar2(15) ) as m_1 ) rules sequential order ITERATE (5) ( m_1[0] = m_1[0] || 'x' , -- Rule 1 m_1[0] = m_1[0] || 'y' -- Rule 2 ) ;

KEY M_1 ------ --------------- 0 xyxyxyxyxy

#### UNTIL()

You can specify an early termination condition using an UNTIL clause.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate (100) UNTIL M_1[0] = 50 ( m_1[0] = m_1[0] + 10 ) ;

KEY M_1 ------ ---------- 0 50

The UNTIL clause is checked at the end of each iteration. This means iterated rules will always be executed at least once.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate (100) UNTIL ( 1 = 1 ) ( m_1[0] = m_1[0] + 10 ) ;

KEY M_1 ------ ---------- 0 10

#### PREVIOUS()

If you need to, you can reference the previous value of a cell in the UNTIL clause using the PREVIOUS() function.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate (100) until PREVIOUS( m_1[0] ) = 50 ( m_1[0] = m_1[0] + 10 ) ;

KEY M_1 ------ ---------- 0 60

#### ITERATION_NUMBER

The ITERATION_NUMBER function lets you access the current iteration number in repetetive rule calculations.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( cast( null as varchar2(60) ) as m_1 ) rules iterate (3) ( m_1[0] = m_1[0] || 'iteration_number=' || to_char( ITERATION_NUMBER ) || ' ' ) ;

KEY M_1 ------ ------------------------------------------------------------ 0 iteration_number=0 iteration_number=1 iteration_number=2

Note that ITERATION_NUMBER starts at "0".

Unlike PREVIOUS(), which can only be used in the UNTIL clause, ITERATION_NUMBER can appear anywhere in a main model rule. In the following example ITERATION_NUMBER is used in the UNTIL clause, as a dimension reference, and in an expression on the right side of a rule.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules upsert iterate (10) until ( ITERATION_NUMBER = 5 ) ( m_1[ITERATION_NUMBER] = ITERATION_NUMBER * 10 ) order by key ;

KEY M_1 ------ ---------- 0 0 1 10 2 20 3 30 4 40 5 50

#### PARTITION BY

When the PARTITION BY clause is used ITERATE(n) causes rules to be iterated n times for each distinct partitioned value. For example, the following query repeats its rule five times for each of the three distinct values in GROUP_1, leading to a total of 15 rule iterations.

select group_1, iteration_count from ( select distinct group_1 from t where group_1 is not null ) t_a model partition by ( group_1 ) dimension by ( 0 as key ) measures ( cast( null as varchar2(15) ) as iteration_count ) rules iterate( 5 ) ( iteration_count[0] = iteration_count[0] || to_char(iteration_number) || ' ' ) ;

GROUP_1 ITERATION_COUNT ---------- --------------- A 0 1 2 3 4 B 0 1 2 3 4 C 0 1 2 3 4

#### Gotchas

You can only use PREVIOUS in the UNTIL clause. If you use it elsewhere you will get an error.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate (5) ( m_1[0] = PREVIOUS(m_1[0]) + 10 ) ; ( m_1[0] = PREVIOUS(m_1[0]) + 10 ) * ERROR at line 11: ORA-32618: incorrect use of MODEL PREVIOUS function

Keep in mind that referencing the current value of a cell on the right hand side of an iterated rule automatically gives you the previous value (assuming no other rules change the cell). Attempting to use PREVIOUS in the example above was a redundant effort.

ITERATION_NUMBER cannot be used in DIMENSION BY or MEASURES clauses.

select key , m_1 from dual model dimension by ( ITERATION_NUMBER as key ) -- not allowed measures ( 0 as m_1 ) rules upsert iterate (10) ( m_1[iteration_number] = iteration_number * 10 ) order by key ; dimension by ( ITERATION_NUMBER as key ) -- not allowed * ERROR at line 7: ORA-00904: "ITERATION_NUMBER": invalid identifier

select key , m_1 from dual model dimension by ( 0 as key ) measures ( ITERATION_NUMBER as m_1 ) -- not allowed rules upsert iterate (10) ( m_1[iteration_number] = iteration_number * 10 ) order by key ; measures ( ITERATION_NUMBER as m_1 ) -- not allowed * ERROR at line 8: ORA-00904: "ITERATION_NUMBER": invalid identifier

If you specify more than 4,294,967,295 iterations you will get an error.

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate ( 4294967296 ) UNTIL ( 1 = 1 ) ( m_1[0] = m_1[0] + 10 ) ; iterate ( 4294967296 ) UNTIL ( 1 = 1 ) * ERROR at line 10: ORA-32607: invalid ITERATE value in MODEL clause

select key , m_1 from dual model dimension by ( 0 as key ) measures ( 0 as m_1 ) rules iterate ( binary_float_infinity ) UNTIL ( 1 = 1 ) ( m_1[0] = m_1[0] + 10 ) ; iterate ( binary_float_infinity ) UNTIL ( 1 = 1 ) * ERROR at line 10: ORA-32607: invalid ITERATE value in MODEL clause