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