Home

repost: SQL Features Tutorials: MODEL Clause

# MODEL Clause

This section presents tutorials on the MODEL clause of the SELECT command. Introduced in Oracle 10g, the MODEL clause is a powerful feature that gives you the ability to change any cell in the query’s result set using data from any other cell (similar to the way a spreadsheet works). It also adds procedural features to SQL previously available only through PL/SQL calls.

For example, with MODEL you can take a simple table like this

1
2
3
4
5
6
7
8
9
10
11
12
   KEY GROUP_1    GROUP_2    DATE_VAL   NUM_VAL
------ ---------- ---------- ---------- -------
     1 A          a1         2005-01-01     100
     2 A          a2         2005-06-12     200
     3 A          a3                        300
     4 B          a1         2006-02-01
     5 B          a2         2006-06-12     300
     6 B          a3         2005-01-01     100
     7 C          a1         2006-06-12     100
     8 C          a2
     9            a1         2005-02-01     200
    10            a2         2005-02-01     800

and, with a single command, create a report containing ad-hoc totals like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
set null ""

select
  case when key like 'Total%' then key else null end as total ,
  group_1 ,
  group_2 ,
  num_val
from
  t
model
  dimension by
  (
    cast(key as varchar2(20)) as key ,
    nvl( group_1, 'n/a' )  as group_1 ,
    nvl( group_2, 'n/a' )  as group_2
  )
  measures( num_val )
  rules
  ( num_val[ 'Total 1 - A + C', null, null ] =
      sum(num_val)[any,group_1 in ('A','C'),any]
      ,
    num_val[ 'Total 2 - A + a2', null, null ] =
      sum(num_val)[any,'A',any] +
      sum(num_val)[any,group_1 <> 'A','a2']
      ,
    num_val[ 'Total 3 - n/a', null, null ] =
      sum(num_val)[any,'n/a',any]
      ,
    num_val[ 'Total 4 - a1 + a3', null, null ] =
      sum(num_val)[any,any,group_2 in ('a1','a3')]
  )
order by
  group_1 ,
  group_2 ,
  total nulls first
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
TOTAL                GROUP_1    GROUP_2    NUM_VAL
-------------------- ---------- ---------- -------
                     A          a1             100
                     A          a2             200
                     A          a3             300
                     B          a1
                     B          a2             300
                     B          a3             100
                     C          a1             100
                     C          a2
                     n/a        a1             200
                     n/a        a2             800
Total 1 - A + C                                700
Total 2 - A + a2                              1700
Total 3 - n/a                                 1000
Total 4 - a1 + a3                              800

You can also use MODEL’S procedural features to produce results that are difficult, inefficient, or impossible to do with a non-MODEL SELECT command. Here is an example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set null "(null)"
column string format a40

select group_1, substr( string, 2 ) as string
from   t
where  num_val is not null
model
  return updated rows
  partition by ( group_1 )
  dimension by ( row_number() over (partition by group_1 order by num_val) as position )
  measures     ( cast( num_val as varchar2(65) ) as string  ) -- Note 1
  rules
    upsert
    iterate( 6 )
      until ( presentv(string[iteration_number+2],1,0) = 0 )
    ( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_1 ;
1
2
3
4
5
6
GROUP_1    STRING
---------- ----------------------------------------
A          100,200,300
B          100,300
C          100
(null)     200,800

(This last technique is explained fully in another section of SQL Snippets at Rows to String: MODEL Method 1.)

Though powerful, the MODEL clause is also somewhat complex and this can be intimidating when you read about it for the first time. The tutorials to follow will therefore present very simple MODEL examples to help you quickly become comfortable with its many features.

Before continuing it is important to know that everything in the MODEL clause is evaluated after all other clauses in the query, except for SELECT DISTINCT and ORDER BY. Knowing this will help you better understand the examples in this section’s tutorials.

# DIMENSION BY

In this tutorial we learn about the DIMENSION BY component of the MODEL clause.

DIMENSION BY specifies which columns in a SELECT statement are dimension columns, which for our purposes can be thought of as any column that serves to identify each row in the result of a SELECT statement. By default, the dimension columns in a MODEL clause must produce a unique key for the result set. See the Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Glossary for a formal definition.

Before we begin please note that, on its own, DIMENSION BY has little visible effect on the output of the SELECT statement. Most of the examples below would produce the same result as one with no MODEL clause at all. This is because we are not trying to manipulate the results just yet. We are simply seeing how to specify our dimension columns, which is a precursor for learning to manipulate results in subsequent pages.

Consider the following table.

1
2
3
4
5
6
7
8
9
10
11
select
  key     ,
  key_2   ,
  group_1 ,
  group_2 ,
  num_val
from
  t
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY KEY_2 GROUP_1    GROUP_2    NUM_VAL
------ ----- ---------- ---------- -------
     1 T-1   A          a1             100
     2 T-2   A          a2             200
     3 T-3   A          a3             300
     4 T-4   B          a1
     5 T-5   B          a2             300
     6 T-6   B          a3             100
     7 T-7   C          a1             100
     8 T-8   C          a2
     9 T-9              a1             200
    10 T-10             a2             800

We see that KEY, KEY_2, and (GROUP_1, GROUP_2) all uniquely identify each row in the table. They are therefore dimension column candidates. To let Oracle know which column(s) we plan to use as dimensions we compose a MODEL clause like this. (Ignore the MEASURES and RULES clauses for now. We will explore those later.)

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  num_val
from
  t
model
  DIMENSION BY ( KEY )
  measures     ( num_val )
  rules        ()
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL
------ -------
     1     100
     2     200
     3     300
     4
     5     300
     6     100
     7     100
     8
     9     200
    10     800

# Multiple Dimensions

If needed, you can define more than one dimension column, as this example shows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
  group_1 ,
  group_2 ,
  num_val
from
  t
model
  DIMENSION BY ( GROUP_1, GROUP_2 )
  measures     ( num_val )
  rules        ()
order by
  group_1,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    NUM_VAL
---------- ---------- -------
A          a1             100
A          a2             200
A          a3             300
B          a1
B          a2             300
B          a3             100
C          a1             100
C          a2
           a1             200
           a2             800

You can even include columns in the DIMENSION BY clause which are not required to uniquely identify each result row.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  date_val ,
  num_val
from
  t
model
  DIMENSION BY ( KEY, DATE_VAL ) -- date_val not required to uniquely identify row
  measures     ( num_val )
  rules        ()
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY DATE_VAL   NUM_VAL
------ ---------- -------
     1 2005-01-01     100
     2 2005-06-12     200
     3                300
     4 2006-02-01
     5 2006-06-12     300
     6 2005-01-01     100
     7 2006-06-12     100
     8
     9 2005-02-01     200
    10 2005-02-01     800

# Aliasing

You cannot use SELECT clause aliases in DIMENSION BY. Here are some examples of aliases that will cause errors.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
select
  KEY AS KEY_3 ,
  num_val
from
  t
model
  dimension by ( KEY_3 )
  measures     ( num_val )
  rules        ()
;
  dimension by ( KEY_3 )
                 *
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier



select
  KEY * 10 AS KEY_3 ,
  num_val
from
  t
model
  dimension by ( KEY_3 )
  measures     ( num_val )
  rules        ()
;
  dimension by ( KEY_3 )
                 *
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier



select
  ROWNUM AS KEY_3 ,
  num_val
from
  t
model
  dimension by ( KEY_3 )
  measures     ( num_val )
  rules        ()
;
  dimension by ( KEY_3 )
                 *
ERROR at line 7:
ORA-00904: "KEY_3": invalid identifier

You can however alias such expressions directly in DIMENSION BY.

1
2
3
4
5
6
7
8
9
10
11
12
select
  KEY_3,
  num_val
from
  t
model
  DIMENSION BY ( KEY AS KEY_3 )
  measures     ( num_val )
  rules        ()
order by
  key_3
;
1
2
3
4
5
6
7
8
9
10
11
12
13

     KEY_3 NUM_VAL
---------- -------
         1     100
         2     200
         3     300
         4
         5     300
         6     100
         7     100
         8
         9     200
        10     800
1
2
3
4
5
6
7
8
9
10
11
12
select
  KEY_3,
  num_val
from
  t
model
  DIMENSION BY ( KEY * 10 AS KEY_3 )
  measures     ( num_val )
  rules        ()
order by
  key_3
;
1
2
3
4
5
6
7
8
9
10
11
12
     KEY_3 NUM_VAL
---------- -------
        10     100
        20     200
        30     300
        40
        50     300
        60     100
        70     100
        80
        90     200
       100     800
1
2
3
4
5
6
7
8
9
10
11
12
select
  KEY_3,
  num_val
from
  t
model
  DIMENSION BY ( ROWNUM AS KEY_3 )
  measures     ( num_val )
  rules        ()
order by
  key_3
;
1
2
3
4
5
6
7
8
9
10
11
12
     KEY_3 NUM_VAL
---------- -------
         1     100
         2     200
         3     300
         4
         5     300
         6     100
         7     100
         8
         9     200
        10     800

# Uniqueness

By default, if your DIMENSION BY columns do not give you a unique key for your result set you will get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  group_2 ,
  num_val
from
  t
model
  DIMENSION BY ( GROUP_2 ) -- group_2 is not unique
  measures     ( num_val )
  rules        ()
order by
  group_2
;
  t
  *
ERROR at line 5:
ORA-32638: Non unique addressing in MODEL dimensions

This rule can be relaxed somewhat by specifying UNIQUE SINGLE REFERENCE.

1
2
3
4
5
6
7
8
9
10
11
12
select
  group_2 ,
  num_val
from
  t
model UNIQUE SINGLE REFERENCE
  dimension by ( group_2 )     -- group_2 is not unique
  measures     ( num_val )
  rules        ()
order by
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_2    NUM_VAL
---------- -------
a1             100
a1             100
a1             200
a1
a2             800
a2             200
a2             300
a2
a3             300
a3             100

Note that UNIQUE SINGLE REFERENCE affects the types of RULES you can define. This is explained further in Expressions and Cell References.

# MEASURES

In this tutorial we learn about the MEASURES component of the MODEL clause.

MEASURES specifies which columns in a SELECT are measure columns, which for our purposes can be thought of as any column containing a measurable quantity like a price or a length. See the Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Glossary for a formal definition.

Before we begin please note that, on its own, MEASURES has little visible effect on the output of the SELECT statement. Most of the examples below would produce the same result as one with no MODEL clause at all. This is because we are not trying to manipulate the results just yet. We are simply seeing how to specify our measure columns, which is a precursor to manipulating the results. We will see how to actually manipulate our output when we explore the RULES clause in subsequent tutorials.

Before we see MEASURES in action first consider the following table.

1
2
3
4
5
6
7
8
9
10
11
select
  key ,
  group_1  ,
  group_2  ,
  date_val ,
  num_val
from
  t
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY GROUP_1    GROUP_2    DATE_VAL   NUM_VAL
------ ---------- ---------- ---------- -------
     1 A          a1         2005-01-01     100
     2 A          a2         2005-06-12     200
     3 A          a3                        300
     4 B          a1         2006-02-01
     5 B          a2         2006-06-12     300
     6 B          a3         2005-01-01     100
     7 C          a1         2006-06-12     100
     8 C          a2
     9            a1         2005-02-01     200
    10            a2         2005-02-01     800

If we decide to use KEY as our sole dimension column, then all other columns are available for use as measure columns. To let Oracle know we want to use the NUM_VAL column as our measure we can compose a MODEL clause like this.

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  num_val
from
  t
model
  dimension by ( key )
  MEASURES     ( NUM_VAL )
  rules        ()
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL
------ -------
     1     100
     2     200
     3     300
     4
     5     300
     6     100
     7     100
     8
     9     200
    10     800

If we want to include more measure columns we do it like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  date_val ,
  num_val
from
  t
model
  dimension by ( key )
  MEASURES     ( DATE_VAL, NUM_VAL )
  rules        ()
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY DATE_VAL   NUM_VAL
------ ---------- -------
     1 2005-01-01     100
     2 2005-06-12     200
     3                300
     4 2006-02-01
     5 2006-06-12     300
     6 2005-01-01     100
     7 2006-06-12     100
     8
     9 2005-02-01     200
    10 2005-02-01     800

You can define measures using constants and expressions instead of simple column names, like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
  key ,
  num_val    ,
  num_val_2  ,
  date_val_2 ,
  note
from
  t
model
  dimension by
  ( key )
  MEASURES
  ( num_val ,
    NUM_VAL * 10    AS NUM_VAL_2  ,
    SYSDATE         AS DATE_VAL_2 ,
    'A BRIEF NOTE'  AS NOTE
  )
  rules( )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL  NUM_VAL_2 DATE_VAL_2 NOTE
------ ------- ---------- ---------- ------------
     1     100       1000 2007-02-28 A BRIEF NOTE
     2     200       2000 2007-02-28 A BRIEF NOTE
     3     300       3000 2007-02-28 A BRIEF NOTE
     4                    2007-02-28 A BRIEF NOTE
     5     300       3000 2007-02-28 A BRIEF NOTE
     6     100       1000 2007-02-28 A BRIEF NOTE
     7     100       1000 2007-02-28 A BRIEF NOTE
     8                    2007-02-28 A BRIEF NOTE
     9     200       2000 2007-02-28 A BRIEF NOTE
    10     800       8000 2007-02-28 A BRIEF NOTE

# Dimension and Measure Restrictions

In this tutorial we examine restrictions related to both the DIMENSION BY and MEASURES components of the MODEL clause.

If you try to include columns in your SELECT clause which are not DIMENSION or MEASURE columns you will get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key     ,
  GROUP_1 ,  -- exists in table T, but it is not a DIMENSION or MEASURE column
  num_val
from
  t
model
  dimension by ( key )
  measures     ( num_val )
  rules        ()
order by
  key
;
  GROUP_1 ,  -- exists in table T, but it is not a DIMENSION or MEASURE column
  *
ERROR at line 3:
ORA-32614: illegal MODEL SELECT expression

If you try to use the same column for both a dimension and a measure you will get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  key ,
  num_val
from
  t
model
  DIMENSION BY ( KEY )
  MEASURES     ( KEY, num_val )
  rules        ()
order by
  key
;
  key ,
  *
ERROR at line 2:
ORA-00957: duplicate column name

The constants NULL and ‘’ (empty string), by themselves, are not allowed in DIMENSION BY or MEASURES clauses.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
select
  key   ,
  key_3 ,
  num_val
from
  t
model
  dimension by ( key, NULL as key_3 )
  measures     ( num_val )
  rules        ( )
order by
  key
;
  dimension by ( key, NULL as key_3 )
                      *
ERROR at line 8:
ORA-01723: zero-length columns are not allowed


select
  key ,
  new_text_measure
from
  t
model
  dimension by ( key )
  measures     ( '' as new_text_measure )
  rules        ( )
order by
  key
;
  measures     ( '' as new_text_measure )
                 *
ERROR at line 8:
ORA-01723: zero-length columns are not allowed

Instead, use CAST to specify a datatype and length for NULL or ‘’.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
set null "(null)"

select
  key ,
  key_3 ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key, CAST( NULL AS VARCHAR2(10) ) as key_3 )
  measures    ( CAST( '' AS VARCHAR2(15) ) as new_text_measure )
  rules       ( )
order by
  key
;
1
2
3
  KEY KEY_3      NEW_TEXT_MEASURE
------ ---------- --------------------
     1 (null)     (null)

# Gotchas

Pay attention to implicit data type conversions. In the following example NEW_TEXT_MEASURE is implicitly assigned a datatype with a length of 13 characters, the length of the constant used in the MEASURES clause. Attempting to assign a value that is longer than 13 characters raises a ORA-25137 error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  key ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key )
  measures    ( '13 CHARACTERS' as new_text_measure )
  rules       ( new_text_measure[any] = 'OVER 13 CHARACTERS' )
;
  measures    ( '13 CHARACTERS' as new_text_measure )
                *
ERROR at line 10:
ORA-25137: Data value out of range

Despite the error message’s phrasing, it is really the value “OVER 13 CHARACTERS” which is out of range, not “13 CHARACTERS”.

To avoid the error, use CAST to force a datatype long enough to contain the largest value in the result set.

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  new_text_measure
from
  t
where
  key = 1
model
  dimension by( key )
  measures    ( CAST( '13 CHARACTERS' as varchar2(4000) ) as new_text_measure )
  rules       ( new_text_measure[any] = 'OVER 13 CHARACTERS' )
;
1
2
3
   KEY NEW_TEXT_MEASURE
------ --------------------
     1 OVER 13 CHARACTERS

# Basic Rules and Cell Assignments

In this tutorial we start to explore the RULES part of the MODEL clause. A rule is simply a statement that assigns a value to a measure.

In this tutorial we will focus on basic rules and cell assignments. Subsequent tutorials will cover other aspects of rules.

# Basic Rules

In the preceeding tutorial we learned how to specify the DIMENSION BY and MEASURES clauses in a SELECT statement like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100          0
     2     200          0
     3     300          0
     4                  0
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

We can use the RULES clause to change any value appearing under a measure column such as M_1 like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  RULES
  ( M_1[ 1                   ] = 100 ,
    M_1[ KEY = 2             ] = 200 ,
    M_1[ KEY BETWEEN 4 AND 7 ] = 300
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100        100
     2     200        200
     3     300          0
     4                300
     5     300        300
     6     100        300
     7     100        300
     8                  0
     9     200          0
    10     800          0

# Cell Assignments

The term “M_1[1]” is called a “cell reference” and it refers to the M_1 column value where the KEY column value is “1” in the SELECT command’s output. Cell references can only refer to measure cells, not dimension cells. When a cell reference is used as the assignment target on the left side of a rule equation it is called a "cell assignment". Note that the term “cell assignment” refers only to the term on the left hand side of the rule equation, not the entire equation.

On the right hand side of a rule cell references do not have a special name. To avoid confusion, we will always refer to the cell(s) targeted by a rule only as a “cell assignment” and we will use “cell reference” in all other cases.

The part between the square brackets, “[]”, is called a “dimension reference”. When a dimension reference appears in a cell assignment it can be a condition, expression, or loop construct (described in subsequent tutorials) targeting one or more dimension values. Dimension references for all other cell references can only contain conditions or expressions.

To create a cell assignment targeting all cells in a measure column use the ANY keyword in the dimension reference.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[ ANY ] = 100 )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100        100
     2     200        100
     3     300        100
     4                100
     5     300        100
     6     100        100
     7     100        100
     8                100
     9     200        100
    10     800        100

You can nest a cell reference within a cell assignment if the nested cell reference only identifies a single cell. Other restrictions on nested cell references are documented at Rules and Restrictions when Using SQL for Modeling.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
column key_equals_num_val_2 format a16

select
  key ,
  num_val ,
  key_equals_num_val_2
from
  t
model
  dimension by ( key )
  measures     ( num_val, 'N' as key_equals_num_val_2 )
  rules        ( key_equals_num_val_2[ NUM_VAL[2]/100 ] = 'Y' )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL KEY_EQUALS_NUM_V
------ ------- ----------------
     1     100 N
     2     200 Y
     3     300 N
     4         N
     5     300 N
     6     100 N
     7     100 N
     8         N
     9     200 N
    10     800 N

A cell whose dimension is null will be targeted by any of the following styles of dimension reference.

  • [any]
  • [group_1 is any]
  • [null]
  • [group_1 is null]

A reference like “[group_1 = null]” will not work.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
  group_1 ,
  using_any ,
  using_is_any ,
  using_null ,
  using_is_null ,
  using_equals_null
from
  t
where
  key in ( 1, 10 )
model
  dimension by( group_1 )
  measures
  (
    'N' as using_any ,
    'N' as using_is_any ,
    'N' as using_null ,
    'N' as using_is_null ,
    'N' as using_equals_null
  )
  rules
  (
    using_any        [ ANY             ] = 'Y' ,
    using_is_any     [ GROUP_1 IS ANY  ] = 'Y' ,
    using_null       [ NULL            ] = 'Y' ,
    using_is_null    [ GROUP_1 IS NULL ] = 'Y' ,
    using_equals_null[ GROUP_1 = NULL  ] = 'Y'
  )
;
1
2
3
4
GROUP_1    USING_ANY USING_IS_ANY USING_NULL USING_IS_NULL USING_EQUALS_NULL
---------- --------- ------------ ---------- ------------- -----------------
A          Y         Y            N          N             N
           Y         Y            Y          Y             N

# Gotchas

Attempting to modify a dimension column with a rule will generate an error. The left side of a rule can only reference measure cells.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  key ,
  num_val
from
  t
model
  dimension by ( key )
  measures     ( num_val )
  rules        ( KEY[1] = 0 )
order by
  key
;
  rules        ( KEY[1] = 0 )
                 *
ERROR at line 9:
ORA-00904: : invalid identifier

Specifying too few dimension values in a cell reference results in an ORA-00947 error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
  key ,
  m_1
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( 0 as m_1 )
  rules        ( m_1['A'] = 100 )
;
  rules        ( m_1['A'] = 100 )
                    *
ERROR at line 9:
ORA-00947: not enough values

Specifying too many dimension values in a cell reference results in the same ORA-00947 error as the last example. In this case the error text “not enough values” is misleading.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
  key ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( 0 as m_1 )
  rules        ( m_1[1,2] = 100 )
;
  rules        ( m_1[1,2] = 100 )
                    *
ERROR at line 9:
ORA-00947: not enough values

Including a dimension column on the right side of a dimension reference expression will produce an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  group_1 ,
  group_2 ,
  m_1
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( 'N' as m_1 )
  rules        ( m_1[ any, group_2 > GROUP_1 ] = 'Y' )
order by
  group_1
;
  rules        ( m_1[ any, group_2 > GROUP_1 ] = 'Y' )
                                     *
ERROR at line 10:
ORA-32625: illegal dimension in cell reference predicate

A nested cell reference that targets more than one cell will produce an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[ NUM_VAL[KEY IN (1,2,3)] ] = 1 )
order by
  key
;
select
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference

# FOR Loops

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on FOR loops. FOR loops allow you to take a MODEL clause like this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[2] = 'Y' ,
    flag[4] = 'Y' ,
    flag[6] = 'Y'
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .

and rewrite it like this …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY IN ( 2, 4, 6 ) ] = 'Y'
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .

or this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY FROM 2 TO 6 INCREMENT 2 ] = 'Y'
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .

or this.

1
select key from t2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
   KEY
------
     2
     4
     6

select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY IN ( SELECT KEY FROM T2 ) ] = 'Y'
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .

With “FOR dimension LIKE …” syntax you can combine strings with incrementing/decrementing values like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  key_2 ,
  flag
from
  t
model
  dimension by ( key_2 )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY_2 LIKE 'T-%' FROM 2 TO 6 INCREMENT 2 ] = 'Y'
  )
order by
  key_2
;
1
2
3
4
5
6
7
8
9
10
11
12
KEY_2 FLAG
----- ----
T-1   .
T-10  .
T-2   Y
T-3   .
T-4   Y
T-5   .
T-6   Y
T-7   .
T-8   .
T-9   .

# Multiple Dimensions

To specify dimension value pairs, triplets, etc. you can use a FOR loop like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  group_1 ,
  group_2 ,
  flag
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( '.' as flag )
  rules
  (
    flag
    [ FOR ( GROUP_1, GROUP_2 ) IN
      (
        ( 'A', 'a1' ) ,
        ( 'B', 'a2' ) ,
        ( 'C', 'a1' )
      )
    ] = 'Y'
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    FLAG
---------- ---------- ----
A          a1         Y
A          a2         .
A          a3         .
B          a1         .
B          a2         Y
B          a3         .
C          a1         Y
C          a2         .
           a1         .
           a2         .

A multi-dimension FOR loop with a subquery looks like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
  group_1 ,
  group_2 ,
  num_val ,
  flag
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( num_val, '.' as flag )
  rules
  (
    flag
    [ FOR ( GROUP_1, GROUP_2 ) IN
      ( SELECT GROUP_1, GROUP_2 FROM T WHERE NUM_VAL IS NULL )
    ] = 'Y'
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    NUM_VAL FLAG
---------- ---------- ------- ----
A          a1             100 .
A          a2             200 .
A          a3             300 .
B          a1                 Y
B          a2             300 .
B          a3             100 .
C          a1             100 .
C          a2                 Y
           a1             200 .
           a2             800 .

# FOR versus IN

It is important to note that a dimension reference with a FOR loop like FOR KEY IN ( 2, 4, 6 ) differs from one with an IN expression like KEY IN ( 2, 4, 6 ) . This is explained further in the Missing Cells - UPSERT tutorial.

# Expressions and Cell References

In this tutorial we continue exploring the RULES part of the MODEL clause. We will focus on cell references in the right hand side of rule equations. Subsequent tutorials will cover other aspects of rules.

# Expressions

In the Basic Rules and Cell Assignments tutorial we saw rules like these which assign the constants 100, 200, and 300 to certain cells.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules
  ( M_1[ 1 ] = 100 ,
    M_1[ 2 ] = 200 ,
    M_1[ 3 ] = 300
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100        100
     2     200        200
     3     300        300
     4                  0
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

We can also use more complex expressions on the right hand side of a rule, like these.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
variable BIND_VAR number

execute :BIND_VAR := 4000000

select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules
  ( m_1[ 1 ] = NUM_VAL[ 1 ] * 10 ,
    m_1[ 2 ] = TO_NUMBER( TO_CHAR( SYSDATE, 'YYYYMMDD' ) ),
    m_1[ 3 ] = DBMS_UTILITY.GET_TIME ,
    m_1[ 4 ] = :BIND_VAR
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100       1000
     2     200   20070228
     3     300    9081636
     4            4000000
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

# Cell References

The term “NUM_VAL[1]” in the last example is called a “cell reference”. Cell references can only refer to measure cells and they must only refer to a single measure cell when used on the right hand side of a rule.

The part between the square brackets in a cell reference is called a dimension reference and it can only contain literal values (e.g. “1”), expressions (e.g. “1+1”), or conditions (e.g. “KEY=3”) that resolve to a single dimension value. Here are some examples.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules
  ( m_1[ 1 ] = NUM_VAL[ 1     ] * 10 ,   -- literal
    m_1[ 2 ] = NUM_VAL[ 1 + 1 ] * 100 ,  -- expression
    m_1[ 3 ] = NUM_VAL[ KEY=3 ] * 1000   -- condition
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100       1000
     2     200      20000
     3     300     300000
     4                  0
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

If required, you can nest one cell reference within another as long as the nested cell reference only identifies a single cell. Other restrictions on nested cell references are documented at Rules and Restrictions when Using SQL for Modeling.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[ NUM_VAL[1]/100 ] = 10 )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100         10
     2     200          0
     3     300          0
     4                  0
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

# UNIQUE SINGLE REFERNCE

As described in DIMENSION BY the UNIQUE SINGLE REFERENCE feature allows you to have duplicate domain values in your result set. You can only use this feature as long as you do not include any multi-cell references on the right hand side of your query rules. For example, this is o.k.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  group_2 ,
  num_val ,
  m_1
from
  t
model UNIQUE SINGLE REFERENCE
  dimension by ( group_2 )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = 10 )
order by
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_2    NUM_VAL        M_1
---------- ------- ----------
a1             100         10
a1             100         10
a1             200         10
a1                         10
a2             800         10
a2             200         10
a2             300         10
a2                         10
a3             300         10
a3             100         10

but this is not

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  group_2 ,
  num_val ,
  m_1
from
  t
model UNIQUE SINGLE REFERENCE
  dimension by ( group_2 )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = NUM_VAL['a2'] * 10 )
;
  t
  *
ERROR at line 6:
ORA-32638: Non unique addressing in MODEL dimensions

and neither is this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  group_2 ,
  num_val ,
  m_1
from
  t
model UNIQUE SINGLE REFERENCE
  dimension by ( group_2 )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = NUM_VAL[CV()] * 10 )
;
  t
  *
ERROR at line 6:
ORA-32638: Non unique addressing in MODEL dimensions

# Gotchas

You cannot reference columns in rules. In the following rule the column NUM_VAL is referenced illegally. Use a cell reference like “NUM_VAL[3]” instead.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[3] = NUM_VAL * 10 )
order by
  key
;
  rules        ( m_1[3] = NUM_VAL * 10 )
                          *
ERROR at line 10:
ORA-00984: column not allowed here

Including a cell reference to a dimension column will produce an error. Only measure columns are allowed in cell references.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  key_x_10
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as key_x_10 )
  rules        ( key_x_10[3] = KEY[3] * 10 )
order by
  key
;
  rules        ( key_x_10[3] = KEY[3] * 10 )
                               *
ERROR at line 10:
ORA-00904: : invalid identifier

Multi-cell references, like NUM_VAL[ANY], are not allowed on the right hand side of a rule.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = NUM_VAL[ANY] )
;
  rules        ( m_1[any] = NUM_VAL[ANY] )
                                    *
ERROR at line 10:
ORA-32622: illegal multi-cell reference

A nested cell reference that targets more than one cell will produce an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[ NUM_VAL[KEY IN (1,2,3)] / 100 ] = 1 )
order by
  key
;
select
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference

Even though a dimension expression resolves to a single value, it may still trigger an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[3] = num_val[KEY BETWEEN 3 AND 3] * 1000 )
order by
  key
;
select
*
ERROR at line 1:
ORA-32622: illegal multi-cell reference

You may not use subqueries in rules, except in the FOR construct (described in FOR Loops).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  key ,
  dummy
from
  t
where
  key = 1
model
  dimension by ( key )
  measures     ( 'Y' as dummy )
  rules        ( m_1[1] = ( SELECT DUMMY FROM DUAL ) )
;
  rules        ( m_1[1] = ( SELECT DUMMY FROM DUAL ) )
                 *
ERROR at line 11:
ORA-00904: : invalid identifier

Instead, define the subquery in the MEASURES list.

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  dummy
from
  t
where
  key = 1
model
  dimension by ( key )
  measures     ( ( SELECT DUMMY FROM DUAL ) as dummy )
  rules        ( )
;
1
2
3
   KEY D
------ -
     1 X

# The CV() Function

In this tutorial we continue exploring the RULES part of the MODEL clause. We will focus on the CV() function here. Subsequent tutorials will cover other aspects of rules.

If we need to access the value of a dimension cell, we cannot do it using a cell reference because cell references can only refer to measure columns. For example, we cannot use a cell reference like KEY[ CV() ] to access the value of KEY cells.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = KEY[CV()] * 10 )
order by
  key
;
  rules        ( m_1[any] = KEY[CV()] * 10 )
                            *
ERROR at line 10:
ORA-00904: : invalid identifier

Fortunately the CV() function exists to helps us access the current value of any dimension cell. In the following example CV(KEY) returns the current value of the KEY dimension column.

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( 0 as m_1 )
  rules        ( m_1[any] = CV(KEY) * 10 )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY        M_1
------ ----------
     1         10
     2         20
     3         30
     4         40
     5         50
     6         60
     7         70
     8         80
     9         90
    10        100

You can use CV() without an argument in dimension references to return the current dimension value associated with its relative position within a cell reference. For example, in the following snippet the first occurrence of CV() refers to the current value of the GROUP_1 dimension and the second occurrence refers to the current value of the GROUP_2 dimension.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  group_1 ,
  group_2 ,
  num_val ,
  m_1
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    num_val,
    0 as m_1
  )
  rules
  (
    m_1[any,any] = NUM_VAL[ CV(), CV() ] * 10
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       1000
A          a2             200       2000
A          a3             300       3000
B          a1
B          a2             300       3000
B          a3             100       1000
C          a1             100       1000
C          a2
           a1             200       2000
           a2             800       8000

CV() also allows us to use relative indexing, as demonstrated in the dimension reference [ CV() - 1 ] for the RUNNING_TOTAL measure in this snippet.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
break on group_1 skip 1 duplicates

select
  key ,
  num_val ,
  running_total
from
  t
model
  dimension by ( key )
  measures
  (
    nvl(num_val,0) as num_val ,
    0 running_total
  )
  rules
  (
    running_total[ 1       ] = num_val[cv()] ,
    running_total[ key > 1 ] = num_val[cv()] + running_total[ CV() - 1 ]
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL RUNNING_TOTAL
------ ------- -------------
     1     100           100
     2     200           300
     3     300           600
     4       0           600
     5     300           900
     6     100          1000
     7     100          1100
     8       0          1100
     9     200          1300
    10     800          2100

# Gotchas

CV() cannot be used on measure columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = cv(NUM_VAL) * 10 )
order by
  key
;
  rules        ( m_1[any] = cv(NUM_VAL) * 10 )
                               *
ERROR at line 10:
ORA-00904: : invalid identifier


CV() cannot be used outside of a dimension reference without an argument.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 as m_1 )
  rules        ( m_1[any] = CV() * 10 )
order by
  key
;
  rules        ( m_1[any] = CV() * 10 )
                            *
ERROR at line 10:
ORA-32611: incorrect use of MODEL CV operator

# PARTITON BY

In this tutorial we explore the PARTITION component of the MODEL clause. The Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 22 describes partition columns this way.

“Partition columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions described in Chapter 21, “SQL for Analysis and Reporting”. Rules in the MODEL clause are applied to each partition independent of other partitions.”

“You can partition data and evaluate rules within each partition independent of other partitions. This enables parallelization of model computation based on partitions.”

# Performance

If your system has multiple processors the PARTITION BY clause may improve your query’s performance (see Parallel Execution). For example, with a query like this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
break on group_1 skip 1 duplicates

select
  group_1 ,
  group_2 ,
  num_val ,
  m_1
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( num_val, 0 m_1 )
  rules        ( m_1[any, any] = num_val[cv(), cv()] * 10 )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       1000
A          a2             200       2000
A          a3             300       3000

B          a1
B          a2             300       3000
B          a3             100       1000

C          a1             100       1000
C          a2

           a1             200       2000
           a2             800       8000

one CPU will most likely process the M_1 rule for all rows in the result set (note: I have not tested this behaviour myself; this information is assumed from the manuals). Using a query like the following produces the same output while allowing one processor on your system to process the rows where GROUP_1 is ‘A’, another CPU to process those where GROUP_1 is ‘B’, etc.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
  group_1 ,
  group_2 ,
  num_val ,
  m_1
from
  t
model
  PARTITION BY ( GROUP_1 )
  dimension by ( group_2 )
  measures     ( num_val, 0 m_1 )
  rules        ( m_1[any] = num_val[cv()] * 10 )  -- note only one dimension now
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       1000
A          a2             200       2000
A          a3             300       3000

B          a1
B          a2             300       3000
B          a3             100       1000

C          a1             100       1000
C          a2

           a1             200       2000
           a2             800       8000

While PARTITION BY had no visible effect on the output in the last example, there are situations where it will have an effect. These are described next.

# Uniqueness of Dimension Columns

In the DIMENSION BY tutorial we saw that a DIMENSION BY clause whose columns do not uniquely identify each result row generate an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select
  group_2 ,
  num_val
from
  t
model
  dimension by ( group_2 ) -- group_2 is not unique
  measures     ( num_val )
  rules        ()
order by
  group_2
;
  t
  *
ERROR at line 5:
ORA-32638: Non unique addressing in MODEL dimensions


With a partition clause this no longer happens, as long as the dimension values are unique within a partition.

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  group_2 ,
  num_val
from
  t
model
  PARTITION BY ( GROUP_1 )
  dimension by ( group_2 ) -- group_2 values are unique within group_1 values
  measures     ( num_val )
  rules        ()
order by
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_2    NUM_VAL
---------- -------
a1             100
a1             100
a1
a1             200
a2             800
a2             200
a2
a2             300
a3             100
a3             300

If your dimension values are not unique within a partition you will get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select
  group_1 ,
  date_val ,
  num_val
from
  t
model
  partition by ( group_1 )
  dimension by ( DATE_VAL ) -- there are duplicate date values in group_1
  measures     ( num_val )
  rules        ()
order by
  group_1 ,
  date_val
;
  t
  *
ERROR at line 6:
ORA-32638: Non unique addressing in MODEL dimensions


# Aggregate and Analytic Functions

PARTITION BY also effects query results with aggregate and analytic functions. This effect is described in the Aggregate and Analytic Expressions tutorial.

# Aggregate and Analytic Functions

Queries with a MODEL clause cannot specify aggregate or analytic functions in their SELECT or ORDER BY lists. For example, this attempt generates an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  group_1 ,
  MIN( GROUP_2 ) as c_1 ,
  MAX( GROUP_2 ) as c_2 ,
  SUM( NUM_VAL ) as c_3
from
  t
group by
  group_1
model
  dimension by( group_1 )
  measures    ( group_2, num_val )
  rules ( )
order by
  group_1
;
  measures    ( group_2, num_val )
                *
ERROR at line 12:
ORA-00979: not a GROUP BY expression


If needed, these functions can instead be specified in the PARTITION BY, DIMENSION BY, MEASURES, or RULES clauses. The following examples demonstrate their use in DIMENSION BY and MEASURES.

# Aggregate Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
column d_1 format a10
column d_2 format a10
column m_1 format 999999

select
  group_1, d_1, d_2, m_1
from
  t
group by
  group_1
model
  dimension by
  (
    group_1 ,
    MIN( GROUP_2 ) as d_1  ,
    MAX( GROUP_2 ) as d_2
  )
  measures
  (
    SUM( NUM_VAL ) as m_1
  )
  rules ( )
order by
  group_1
;
1
2
3
4
5
6
GROUP_1    D_1        D_2            M_1
---------- ---------- ---------- -------
A          a1         a3             600
B          a1         a3             400
C          a1         a2             100
           a1         a2            1000
# Analytic Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
break on group_1 skip 1 duplicates

column d_1 format a10
column d_2 format a10
column m_1 format 999999

select
  group_1, group_2, d_1, d_2, num_val, m_1
from
  t
model
  dimension by
  (
    group_1 ,
    group_2 ,
    MIN( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_1 ,
    MAX( GROUP_2 ) OVER ( PARTITION BY GROUP_1 ) as d_2
  )
  measures
  ( num_val ,
    SUM( NUM_VAL ) OVER ( PARTITION BY GROUP_1 ) as m_1
  )
  rules ( )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2    D_1        D_2        NUM_VAL     M_1
---------- ---------- ---------- ---------- ------- -------
A          a1         a1         a3             100     600
A          a2         a1         a3             200     600
A          a3         a1         a3             300     600

B          a1         a1         a3                     400
B          a2         a1         a3             300     400
B          a3         a1         a3             100     400

C          a1         a1         a2             100     100
C          a2         a1         a2                     100

           a1         a1         a2             200    1000
           a2         a1         a2             800    1000

# RULES

You can also use aggregate and analytic functions in the RULES clause, but it is not as straightforward as using them in DIMENSION BY and MEASURES is. See the tutorial Aggregate and Analytic Expressions for more information on this topic.

# Aggregate and Analytic Expressions

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on aggregate and analytic expressions. (See Model Expressions for full syntax.)

Aggregate and analytic functions can be used on the right hand side of a rule to process multiple cell values. They can not appear on the left hand side of rules. Both types of functions can take constants, bind variables, measure columns, or expressions involving them as arguments. Beyond that, aggregate functions can also specify a cell reference to restrict which cells they operate on. For example, in this snippet

1
2
3
4
5
6
7
8
9
10
11
12
13
select
  key ,
  num_val ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( num_val, 0 m_1 )
  rules        ( m_1[3] = SUM( NUM_VAL )[ KEY IN (1,2,3) ] )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY NUM_VAL        M_1
------ ------- ----------
     1     100          0
     2     200          0
     3     300        600
     4                  0
     5     300          0
     6     100          0
     7     100          0
     8                  0
     9     200          0
    10     800          0

the aggregate function SUM takes the measure column NUM_VAL as an argument. The dimension reference [ KEY IN (1,2,3) ] restricts the function to three specific cell values.

Here is an analytic example. Note that analytic functions are not qualified with dimension references.

1
2
3
4
5
6
7
8
9
10
11
12
select
  key ,
  m_1
from
  t
model
  dimension by ( key )
  measures     ( 0 m_1 )
  rules        ( m_1[any] = ROW_NUMBER() OVER ( ORDER BY KEY DESC ) )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY        M_1
------ ----------
     1         10
     2          9
     3          8
     4          7
     5          6
     6          5
     7          4
     8          3
     9          2
    10          1

The following two snippets perform similar logic. One uses analytic functions and the other aggregate functions. By using the CV() and ANY dimension references with the aggregate functions we see that they can produce the same results analytic functions do.

Aggregate

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
break on group_1 skip 1 duplicates

select
  group_1 ,
  num_val ,
  m_1 ,
  m_2
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    num_val,
    0 m_1,
    0 m_2
  )
  rules
  (
    m_1[any,any] =
      MIN( NUM_VAL )
        [ CV(), ANY ] ,
    m_2[any,any] =
      MAX( NUM_VAL )
        [ CV(), ANY ]
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    NUM_VAL        M_1        M_2
---------- ------- ---------- ----------
A              100        100        300
A              200        100        300
A              300        100        300

B                         100        300
B              300        100        300
B              100        100        300

C              100        100        100
C                         100        100

               200        200        800
               800        200        800

Analytic

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
break on group_1 skip 1 duplicates

select
  group_1 ,
  num_val ,
  m_1 ,
  m_2
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    num_val,
    0 m_1,
    0 m_2
  )
  rules
  (
    m_1[any,any] =
      MIN(NUM_VAL)
      OVER (PARTITION BY (GROUP_1)),
    m_2[any,any] =
      MAX(NUM_VAL)
      OVER (PARTITION BY (GROUP_1))
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    NUM_VAL        M_1        M_2
---------- ------- ---------- ----------
A              100        100        300
A              200        100        300
A              300        100        300

B                         100        300
B              300        100        300
B              100        100        300

C              100        100        100
C                         100        100

               200        200        800
               800        200        800

# PARTITION BY and Aggregate Functions

To see how PARTITION BY affects aggregate functions, consider the following snippets. The one on the left does not use PARTITION BY. The one on the right does. As you can see, using PARTITION BY produces different values under M_1 than not using it.

Without PARTITION BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
break on group_1 skip 1 duplicates

select
  group_1 ,
  group_2 ,
  num_val ,
  m_1
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( num_val, 0 m_1 )
  rules
  ( m_1[any, any] =
      SUM(NUM_VAL)[ANY, ANY] )
order by
  group_1 ,
  group_2 ,
  num_val
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100       2100
A          a2             200       2100
A          a3             300       2100

B          a1                       2100
B          a2             300       2100
B          a3             100       2100

C          a1             100       2100
C          a2                       2100

           a1             200       2100
           a2             800       2100

With PARTITION BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
break on group_1 skip 1 duplicates

select
  group_1 ,
  group_2 ,
  num_val ,
  m_1
from
  t
model
  PARTITION BY ( GROUP_1 )
  dimension by ( group_2 )
  measures     ( num_val , 0 m_1 )
  rules
  ( m_1[any] = SUM(NUM_VAL)[ANY] )
order by
  group_1 ,
  group_2 ,
  num_val
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2    NUM_VAL        M_1
---------- ---------- ------- ----------
A          a1             100        600
A          a2             200        600
A          a3             300        600

B          a1                        400
B          a2             300        400
B          a3             100        400

C          a1             100        100
C          a2                        100

           a1             200       1000
           a2             800       1000

# PARTITION BY and Analytic Functions

To see how PARTITION BY affects analytic functions, consider the following snippets. The one on the left does not use PARTITION BY. The one on the right does. Again, using PARTITION BY produces different values under M_1 than not using it.

Without PARTITION BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
break on group_1 skip 1 duplicates

select
  group_1 ,
  group_2 ,
  m_1
from
  t
model
  --
  dimension by ( group_1, group_2 )
  measures     ( 0 m_1 )
  rules
  (
    m_1[any, any] =
      ROW_NUMBER()
      OVER(ORDER BY GROUP_1,GROUP_2)
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2           M_1
---------- ---------- ----------
A          a1                  1
A          a2                  2
A          a3                  3

B          a1                  4
B          a2                  5
B          a3                  6

C          a1                  7
C          a2                  8

           a1                  9
           a2                 10

With PARTITION BY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
break on group_1 skip 1 duplicates

select
  group_1 ,
  group_2 ,
  m_1
from
  t
model
  PARTITION BY ( GROUP_1 )
  dimension by ( group_2 )
  measures     ( 0 m_1 )
  rules
  (
    m_1[any] =
      ROW_NUMBER()
      OVER(ORDER BY GROUP_1, GROUP_2)
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
GROUP_1    GROUP_2           M_1
---------- ---------- ----------
A          a1                  1
A          a2                  2
A          a3                  3

B          a1                  1
B          a2                  2
B          a3                  3

C          a1                  1
C          a2                  2

           a1                  1
           a2                  2

# Missing Cells - UPSERT

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on UPSERT behaviour with cell assignments for missing cells.

By default, if a cell assignment targets a cell that does not exist then UPSERT logic is used to process the results. With UPSERT logic Oracle may or may not add a new cell (plus the associated cells in the new cell’s row) to the result set when it encounters a nonexistent dimension value, depending on the style of dimension reference used.

If a dimension reference uses a constant expression (aka “positional references”) then existing cells are updated and missing cells are created. In the example below the cell assignment with the KEY value “5” points to an existing cell, so it updates the cell to “5”. The ones with KEY values “0” and “-1” point to nonexistent dimension values so new cells are added to the results.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPSERT
  (
    m_1[ 0                 ] = 1, -- positional ref., nonexistent value
    m_2[ 5                 ] = 2, -- positional ref., existing value
    m_3[ FOR KEY IN (-1,5) ] = 3  -- positional ref., mixed
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
    -1                                      3
     0                1
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

Note that FOR loops like FOR KEY IN (-1,5) are considered positional references.

If the dimension reference uses a boolean expression (aka a “symbolic reference”) that evaluates to FALSE, i.e. the cell assignment points to a missing cell, then the rule is ignored. In this example the rule for M_1 is applied where KEY = 5 is TRUE. The rule for M_2 is never applied because KEY = 0 is always FALSE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPSERT
  (
    m_1[ KEY = 0       ] = 1 ,  -- symbolic ref, nonexistent dimension val
    m_2[ KEY = 5       ] = 2 ,  -- symbolic ref, existing dimension val
    m_3[ KEY IN (-1,5) ] = 3    -- symbolic ref, mixed
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

Note that, unlike FOR KEY IN (-1,5) , the dimension reference KEY IN (-1,5) is a symbolic reference.

UPSERT creates cells only when all the dimension references in a cell are either positional references to existing dimension values or symbolic references that evaluate to TRUE. No cells are created for any other combination. For example, the dimension references for the first and second rules below will not trigger new cells, those for the third and fourth will.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select
  group_1, group_2, key_2, m_1, m_2, m_3, m_4
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    key_2 ,
    cast( null as number) m_1 ,
    cast( null as number) m_2 ,
    cast( null as number) m_3 ,
    cast( null as number) m_4
  )
  rules UPSERT
  (
    m_1[ 'A', GROUP_2 = 'a4' ] = 1 , -- positional, symbolic / existing   , FALSE
    m_2[ 'D', GROUP_2 = 'a3' ] = 2 , -- positional, symbolic / nonexistent, TRUE
    m_3[ 'A', GROUP_2 = 'a3' ] = 3 , -- positional, symbolic / existing   , TRUE
    m_4[ 'A', ANY            ] = 4   -- positional, symbolic / existing   , TRUE
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    KEY_2        M_1        M_2        M_3        M_4
---------- ---------- ----- ---------- ---------- ---------- ----------
A          a1         T-1                                             4
A          a2         T-2                                             4
A          a3         T-3                                  3          4
B          a1         T-4
B          a2         T-5
B          a3         T-6
C          a1         T-7
C          a2         T-8
           a1         T-9
           a2         T-10

In rules like the one for M_4 above note that “ANY” is a symbolic reference because it equates to (GROUP_1 IS NOT NULL OR GROUP_1 IS NULL).

# Gotchas

Given that WHERE clauses are evaluated before MODEL clauses and given that UPSERT may create new cells, be aware that WHERE clauses with FALSE results will not necessarily lead to empty result sets when MODEL is used. Here is a good example of a query some people might expect a “no rows selected” result from, but in fact returns two rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key, key_2, m_1, m_2
from
  t
where
  1=2  -- (1) no rows are returned from table T, initial result set is empty
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast( null as number) m_1 ,
    cast( null as number) m_2
  )
  rules upsert
  (
    M_1[ 0 ] = 1 ,  -- (2) adds a new row to the result set
    M_2[ 5 ] = 1    -- (3) adds another new row to the result set
  )
order by
  key
;
1
2
3
4
   KEY KEY_2        M_1        M_2
------ ----- ---------- ----------
     0                1
     5                           1

In this case, the row with a KEY value of 5 is a new row created by the rule for M2. It is **_not** related to the row in table T with a KEY value of ‘5’.

# Missing Cells - UPSERT ALL

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on UPSERT ALL behaviour with cell assignments for missing cells.

UPSERT ALL behaviour is similar to UPSERT behaviour. If we specify UPSERT ALL for the first two examples used in the Missing Cells - UPSERT tutorial we see that the output for both UPSERT and UPSERT ALL is identical. Missing cells are created when a dimension reference is positional and they are ignored when it is symbolic.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPSERT ALL
  (
    m_1[ 0                 ] = 1 ,  -- positional reference
    m_2[ 5                 ] = 2 ,  -- positional reference
    m_3[ FOR KEY IN (-1,5) ] = 3    -- positional reference
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
    -1                                      3
     0                1
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPSERT ALL
  (
    m_1[ KEY = 0       ] = 1 ,  -- symbolic reference
    m_2[ KEY = 5       ] = 2 ,  -- symbolic reference
    m_3[ KEY IN (-1,5) ] = 3    -- symbolic reference
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

# Existential Predicates

The difference between UPSERT and UPSERT ALL becomes evident when there is a mix of symbolic and positional references. The Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 22 - UPSERT ALL Behavior describes it this way

“UPSERT ALL behavior allows model rules with existential predicates (comparisons, IN, ANY, and so on) in their left side to have UPSERT behavior.”

img
© Copyright Lee, 2007

According to the Wikipedia entry for Existentialism

“existentialism is often associated with anxiety, dread, awareness of death, and freedom.”

Fear not though, existential predicates in SQL are not as angst-ridden as their philosophical namesake. A few examples will clarify matters.

Here is the third example from the Missing Cells - UPSERT tutorial modified to use UPSERT ALL. Unlike with UPSERT, here we see that missing cells are created for M_2 even though it has a nonexistent positional reference.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select
  group_1, group_2, key_2, m_1, m_2, m_3, m_4
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    key_2 ,
    cast( null as number ) m_1 ,
    cast( null as number ) m_2 ,
    cast( null as number ) m_3 ,
    cast( null as number ) m_4
  )
  rules UPSERT ALL
  (
    m_1[ 'A', GROUP_2 = 'a4' ] = 1 , -- positional, symbolic / existing   , FALSE
    m_2[ 'D', GROUP_2 = 'a3' ] = 2 , -- positional, symbolic / nonexistent, TRUE
    m_3[ 'A', GROUP_2 = 'a3' ] = 3 , -- positional, symbolic / existing   , TRUE
    m_4[ 'A', ANY            ] = 4   -- positional, symbolic / existing   , TRUE
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
GROUP_1    GROUP_2    KEY_2        M_1        M_2        M_3        M_4
---------- ---------- ----- ---------- ---------- ---------- ----------
A          a1         T-1                                             4
A          a2         T-2                                             4
A          a3         T-3                                  3          4
B          a1         T-4
B          a2         T-5
B          a3         T-6
C          a1         T-7
C          a2         T-8
D          a3                                   2
           a1         T-9
           a2         T-10

UPSERT ALL created a cell for the M_2 rule because there was at least one row in the initial result set with GROUP_2 = 'a3' , even though it was not paired with any GROUP_1 values of “D”.

Here are some more examples of existential predicates that match at least one row in the result set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select
  group_1, group_2, key_2, m_1, m_2, m_3, m_4
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    key_2 ,
    cast( null as number) m_1 ,
    cast( null as number) m_2 ,
    cast( null as number) m_3 ,
    cast( null as number) m_4
  )
  rules UPSERT ALL
  (
    m_1[ 'D', GROUP_2 >= 'a2'               ] = 1 ,
    m_2[ 'E', GROUP_2 in ( 'a3', 'a4' )     ] = 2 ,
    m_3[ 'F', GROUP_2 = any ( 'a3', 'a5' )  ] = 3 ,
    m_4[ 'G', GROUP_2 not in ( 'a6', 'a7' ) ] = 4
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
GROUP_1    GROUP_2    KEY_2        M_1        M_2        M_3        M_4
---------- ---------- ----- ---------- ---------- ---------- ----------
A          a1         T-1
A          a2         T-2
A          a3         T-3
B          a1         T-4
B          a2         T-5
B          a3         T-6
C          a1         T-7
C          a2         T-8
D          a2                        1
D          a3                        1
E          a3                                   2
F          a3                                              3
G          a1                                                         4
G          a2                                                         4
G          a3                                                         4
           a1         T-9
           a2         T-10

# Missing Cells - UPDATE

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on UPDATE behaviour with cell assignments for missing cells.

Finally, we examine the last and simplest of the three available rule behaviours, UPDATE. Here are the three examples used in the Missing Cells - UPSERT tutorial modified to use UPDATE behaviour. As you can see, missing cells are never created for nonexistent dimension references like ‘-1’, ‘0’, ‘D’, and ‘a4’ when UPDATE is specified.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPDATE
  (
    m_1[ 0                 ] = 1 ,  -- positional reference to nonexistent value
    m_2[ 5                 ] = 2 ,  -- positional reference to existing value
    m_3[ FOR KEY IN (-1,5) ] = 3    -- positional reference to both
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

select
  key, key_2, m_1, m_2, m_3
from
  t
model
  dimension by ( key )
  measures
  (
    key_2 ,
    cast(null as number) m_1 ,
    cast(null as number) m_2 ,
    cast(null as number) m_3
  )
  rules UPDATE
  (
    m_1[ KEY = 0       ] = 1 ,  -- symbolic ref. to nonexistent dimension value
    m_2[ KEY = 5       ] = 2 ,  -- symbolic ref. to existing dimension value
    m_3[ KEY IN (-1,5) ] = 3    -- symbolic ref. to both
  )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
   KEY KEY_2        M_1        M_2        M_3
------ ----- ---------- ---------- ----------
     1 T-1
     2 T-2
     3 T-3
     4 T-4
     5 T-5                       2          3
     6 T-6
     7 T-7
     8 T-8
     9 T-9
    10 T-10

select
  group_1, group_2, key_2, m_1, m_2, m_3, m_4
from
  t
model
  dimension by ( group_1, group_2 )
  measures
  (
    key_2 ,
    cast( null as number) m_1 ,
    cast( null as number) m_2 ,
    cast( null as number) m_3 ,
    cast( null as number) m_4
  )
  rules UPDATE
  (
    m_1['A', GROUP_2='a4'] = 1, -- positional, symbolic / existing, nonexistent
    m_2['D', GROUP_2='a3'] = 2, -- positional, symbolic / nonexistent, existing
    m_3['A', GROUP_2='a3'] = 3, -- positional, symbolic / existing, existing
    m_4['A', ANY         ] = 4  -- positional, symbolic / existing, existing
  )
order by
  group_1 ,
  group_2
;
1
2
3
4
5
6
7
8
9
10
11
12
GROUP_1    GROUP_2    KEY_2        M_1        M_2        M_3        M_4
---------- ---------- ----- ---------- ---------- ---------- ----------
A          a1         T-1                                             4
A          a2         T-2                                             4
A          a3         T-3                                  3          4
B          a1         T-4
B          a2         T-5
B          a3         T-6
C          a1         T-7
C          a2         T-8
           a1         T-9
           a2         T-10

# Missing Cells - Right Side

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on cell references for missing cells on the right hand side of a rule.

By default, if a cell reference on the right hand side of a rule targets a cell that does not exist, the reference evaluates to NULL. In the following snippet dimension “0” does not exist in the result set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
set null "(null)"

select
  key, num_measure, text_measure, date_measure
from
  t
where
  key = 1
model
  dimension by ( key )
  measures
  (
    num_val ,
    group_1 ,
    date_val ,
    123              as num_measure  ,
    'abcdefghijklmn' as text_measure ,
    sysdate          as date_measure
  )
  rules upsert
  (
    num_measure [1] = num_val [0] ,
    text_measure[1] = group_1 [0] ,
    date_measure[1] = date_val[0]
  )
;
1
2
3
   KEY NUM_MEASURE TEXT_MEASURE   DATE_MEASU
------ ----------- -------------- ----------
     1 (null)      (null)         (null)

Note that missing cell references are relative to the query results, not the base table. For example, in the following snippets dimension reference [2] points to an existing cell in the first snippet and a missing cell in the second.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  key, num_measure, text_measure, date_measure
from
  t
where
  KEY = 2
model
  dimension by ( key )
  measures
  (
    num_val ,
    group_1 ,
    date_val ,
    123              as num_measure  ,
    'abcdefghijklmn' as text_measure ,
    sysdate          as date_measure
  )
  rules upsert
  (
    num_measure [any] = num_val [2] ,
    text_measure[any] = group_1 [2] ,
    date_measure[any] = date_val[2]
  )
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
   KEY NUM_MEASURE TEXT_MEASURE   DATE_MEASU
------ ----------- -------------- ----------
     2         200 A              2005-06-12

select
  key, num_measure, text_measure, date_measure
from
  t
where
  KEY = 1  -- row with KEY = '2' is not included this time
model
  dimension by ( key )
  measures
  (
    num_val ,
    group_1 ,
    date_val ,
    123              as num_measure  ,
    'abcdefghijklmn' as text_measure ,
    sysdate          as date_measure
  )
  rules upsert
  (
    num_measure [any] = num_val [2] ,
    text_measure[any] = group_1 [2] ,
    date_measure[any] = date_val[2]
  )
;
1
2
3
   KEY NUM_MEASURE TEXT_MEASURE   DATE_MEASU
------ ----------- -------------- ----------
     1 (null)      (null)         (null)

# IGNORE NAV

You can control missing cell behaviour with the IGNORE NAV option which causes Oracle to return

  • 0 for missing numeric data
  • an empty string for missing character/string data
  • 01-JAN-2000 for missing date data
  • NULL for all other data types.
1
alter session set nls_date_format = 'YYYY-MM-DD';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  key, num_measure, text_measure, date_measure
from
  t
where
  key = 1
model
  IGNORE NAV
  dimension by ( key )
  measures
  (
    num_val ,
    group_1 ,
    date_val ,
    123          as num_measure  ,
    'abcdefghij' as text_measure ,
    sysdate      as date_measure
  )
  rules upsert
  ( num_measure [1] = num_val [0] ,
    text_measure[1] = group_1 [0] ,
    date_measure[1] = date_val[0]
  )
;
1
2
3
   KEY NUM_MEASURE TEXT_MEASU DATE_MEASU
------ ----------- ---------- ----------
     1           0 (null)     2000-01-01

Note that in Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 22 - Treatment of NULLs and Missing Cells it incorrectly states that “01-JAN-2001” is returned for missing data data. The correct value, “01-JAN-2000”, is documented in Oracle® Database SQL Reference 10g Release 2 (10.2) - SELECT - model_clause.

# IS PRESENT, PRESENTV, and PRESENTNNV

If you need to distinguish between NULL values from your data and NULL values representing missing cells you can use the IS PRESENT condition or the PRESENTV and PRESENTNNV functions on the right hand side of a rule.

Here is an example of their use. Note that cell references with a dimension of “0” point to cells that were missing from the result set before the MODEL clause was executed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
select
  key, num_val, num_val_is_present, presentv_num_val, presentnnv_num_val
from
  t
where
  key in ( 1, 4 )
model
  dimension by ( key )
  measures
  (
    num_val ,
    cast ( 'default' as varchar2(20) ) as num_val_is_present ,
    cast ( 'default' as varchar2(20) ) as presentv_num_val   ,
    cast ( 'default' as varchar2(20) ) as presentnnv_num_val
  )
  rules upsert
  (
    num_val_is_present[0] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
    num_val_is_present[1] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
    num_val_is_present[4] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
    --
    presentv_num_val  [0] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'),
    presentv_num_val  [1] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'),
    presentv_num_val  [4] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'),
    --
    presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2')
  )
order by
  key
;
1
2
3
4
5
   KEY NUM_VAL NUM_VAL_IS_PRESENT   PRESENTV_NUM_VAL     PRESENTNNV_NUM_VAL
------ ------- -------------------- -------------------- --------------------
     0 (null)  FALSE                expr 2               expr 2
     1     100 TRUE                 expr 1               expr 1
     4 (null)  TRUE                 expr 1               expr 2

It is important to note that these three functions consider a cell missing if it does not exist before the MODEL clause is executed. They will return the same results as above even when the cell they reference is created, via a rule, before the functions are called. The following variation of the last snippet demonstrates this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
select
  key, num_val, num_val_is_present, presentv_num_val, presentnnv_num_val
from
  t
where
  key in ( 1, 4 )
model
  dimension by ( key )
  measures
  (
    num_val ,
    cast ( 'default' as varchar2(20) ) as num_val_is_present ,
    cast ( 'default' as varchar2(20) ) as presentv_num_val   ,
    cast ( 'default' as varchar2(20) ) as presentnnv_num_val
  )
  rules upsert
  (
    num_val[0] = 0 , --(1)
    --
    num_val_is_present[0] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end , --(2)
    num_val_is_present[1] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
    num_val_is_present[4] =
      case when num_val[cv()] IS PRESENT then 'TRUE' else 'FALSE' end ,
    --
    presentv_num_val  [0] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'), --(3)
    presentv_num_val  [1] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'),
    presentv_num_val  [4] = PRESENTV  (num_val[cv()], 'expr 1', 'expr 2'),
    --
    presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'), --(4)
    presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2')
  )
order by
  key
--
-- (1) num_val[0] is created
-- (2) still yields FALSE
-- (3) still yields 'expr 2'
-- (4) still yields 'expr 2'
;
1
2
3
4
5
   KEY NUM_VAL NUM_VAL_IS_PRESENT   PRESENTV_NUM_VAL     PRESENTNNV_NUM_VAL
------ ------- -------------------- -------------------- --------------------
     0       0 FALSE                expr 2               expr 2
     1     100 TRUE                 expr 1               expr 1
     4 (null)  TRUE                 expr 1               expr 2

Other SQL functions, such as PRESENTNNV’s close cousin NVL2, operate on cells as they appear at the time the function is called. The following two snippets demonstrate the difference between PRESENTNNV and NVL2. 1

In this first snippet the results are the same for both functions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select
  key, num_val, presentnnv_num_val, nvl2_num_val
from
  t
where
  key in ( 1, 4 )
model
  dimension by ( key )
  measures
  (
    num_val ,
    cast ( 'default' as varchar2(20) ) as num_val_is_present ,
    cast ( 'default' as varchar2(20) ) as presentv_num_val   ,
    cast ( 'default' as varchar2(20) ) as presentnnv_num_val ,
    cast ( 'default' as varchar2(20) ) as nvl2_num_val
  )
  rules upsert
  (
    presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    --
    nvl2_num_val      [0] = NVL2      (num_val[cv()], 'expr 1', 'expr 2'),
    nvl2_num_val      [1] = NVL2      (num_val[cv()], 'expr 1', 'expr 2'),
    nvl2_num_val      [4] = NVL2      (num_val[cv()], 'expr 1', 'expr 2')
  )
order by
  key
;
1
2
3
4
5
   KEY NUM_VAL PRESENTNNV_NUM_VAL   NVL2_NUM_VAL
------ ------- -------------------- --------------------
     0 (null)  expr 2               expr 2
     1     100 expr 1               expr 1
     4 (null)  expr 2               expr 2

In this next snippet the first rule creates cell “NUM_VAL[0]”. The results for NVL2 now differ from those of PRESENTNNV.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
select
  key, num_val, presentnnv_num_val, nvl2_num_val
from
  t
where
  key in ( 1, 4 )
model
  dimension by ( key )
  measures
  (
    num_val ,
    cast ( 'default' as varchar2(20) ) as presentnnv_num_val ,
    cast ( 'default' as varchar2(20) ) as nvl2_num_val
  )
  rules upsert
  (
    num_val[0] = 0 , --(1)
    --
    presentnnv_num_val[0] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'), --(2)
    presentnnv_num_val[1] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    presentnnv_num_val[4] = PRESENTNNV(num_val[cv()], 'expr 1', 'expr 2'),
    --
    nvl2_num_val      [0] = NVL2      (num_val[cv()], 'expr 1', 'expr 2'), --(3)
    nvl2_num_val      [1] = NVL2      (num_val[cv()], 'expr 1', 'expr 2'),
    nvl2_num_val      [4] = NVL2      (num_val[cv()], 'expr 1', 'expr 2')
  )
order by
  key
--
-- (1) num_val[0] is created
-- (2) still yields 'expr 2'
-- (3) yields 'expr 1' this time
;
1
2
3
4
5
   KEY NUM_VAL PRESENTNNV_NUM_VAL   NVL2_NUM_VAL
------ ------- -------------------- --------------------
     0       0 expr 2               expr 1
     1     100 expr 1               expr 1
     4 (null)  expr 2               expr 2

# Acknowledgements

  1. Re: PRESENTNNV vs NVL2 by OTN user martyn

# ITERATE

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.

1
2
3
4
5
6
7
8
9
10
11
12
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' )
;
1
2
3
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
    )
;
1
2
3
   KEY M_1
------ ---------------
     0 xyxyxyxyxy

# UNTIL()

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

1
2
3
4
5
6
7
8
9
10
11
12
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 )
;
1
2
3
   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.

1
2
3
4
5
6
7
8
9
10
11
12
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 )
;
1
2
3
   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.

1
2
3
4
5
6
7
8
9
10
11
12
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 )
;
1
2
3
   KEY        M_1
------ ----------
     0         60

# ITERATION_NUMBER

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 ) ||
        ' '
    )
;
1
2
3
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
;
1
2
3
4
5
6
7
8
   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.

1
2
3
4
5
6
7
8
9
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) || ' ' )
;
1
2
3
4
5
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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

# SEQUENTIAL and AUTOMATIC ORDER

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
;
1
2
3
4
5
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
;
1
2
3
4
5
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
;
1
2
3
4
5
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  )
;
1
2
3
   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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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

# Ordered Rules

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on rules that use an ORDER BY clause, also known as ordered rules.

In addition to controlling the evaluation order of different rules, as we saw with sequential and automatic ordering in a previous tutorial, you can also control the assignment order of different cells targeted by a single rule. To do this we specify an ORDER BY clause after a cell reference. This is useful when a rule uses the ANY keyword or a symbolic reference on its left side. Here is an example of two very similar rules. One processes cells in ascending domain value order. The other processes cells in descending domain value order.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' , -- rule 1
    m2[any] ORDER BY KEY DESC = m2[ cv(key) - 1 ] || '* '   -- rule 2
  )
order by
  key
;
1
2
3
4
5
6
7
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    *
     2 * *                  -*
     3 * * *                -*
     4 * * * *              -*
     5 * * * * *            -*

To understand these results it helps to imagine how the result set appears at different stages in the processing.

# Stage 1

These are the results in stage 1, right after data is selected from table T.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules ()
order by
  key
;
1
2
3
4
5
6
7
   KEY M1                   M2
------ -------------------- --------------------
     1 -                    -
     2 -                    -
     3 -                    -
     4 -                    -
     5 -                    -

# Stage 2

In this stage rule 1 is applied to the result set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' -- rule 1
  )
order by
  key
;
1
2
3
4
5
6
7
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    -
     2 * *                  -
     3 * * *                -
     4 * * * *              -
     5 * * * * *            -

Because of the ORDER BY KEY ASC clause, cell assignments for rule 1 were performed in this order.

  • m1[1] = m1[0] || '_ ’ == null || '_ ’
  • m1[2] = m1[1] || '_ ’ == '_ ’ || '* ’
  • m1[3] = m1[2] || '_ ’ == '_ _ ’ || '_ ’
  • m1[4] = m1[3] || '_ ’ == '_ * _ ’ || '_ ’
  • m1[5] = m1[4] || '_ ’ == '_ * * _ ’ || '_ ’

# Stage 3

In this stage rule 2 is applied.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' , -- rule 1
    m2[any] ORDER BY KEY DESC = m2[ cv(key) - 1 ] || '* '   -- rule 2
  )
order by
  key
;
1
2
3
4
5
6
7
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    *
     2 * *                  -*
     3 * * *                -*
     4 * * * *              -*
     5 * * * * *            -*

Because of the ORDER BY KEY DESC clause the cell assignments for rule 2 were performed in this order.

  • m2[5] = m2[4] || '_ ’ == ‘-’ || '_ ’
  • m2[4] = m2[3] || '_ ’ == ‘-’ || '_ ’
  • m2[3] = m2[2] || '_ ’ == ‘-’ || '_ ’
  • m2[2] = m2[1] || '_ ’ == ‘-’ || '_ ’
  • m2[1] = m2[0] || '_ ’ == null || '_ ’

# RETURN ROWS

In this tutorial we explore the RETURN ROWS component of the MODEL clause. With it you can control whether all rows selected or only those rows updated by model rules are returned by the query.

# RETURN ALL ROWS

RETURN ALL ROWS is the default behaviour for MODEL queries. It causes the query to returns all rows selected whether they were updated by a rule or not.

1
2
3
4
5
6
7
8
9
10
break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN ALL ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY ROW_STATUS
------ ----------
     1 UPDATED
     2 x
     3 UPDATED
     4 x
     5 UPDATED
     6 x
     7 UPDATED
     8 x
     9 x
    10 x

# RETURN UPDATED ROWS

RETURN UPDATED ROWS causes the query to return only those rows updated by a rule.

1
2
3
4
5
6
7
8
9
10
break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN UPDATED ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
1
2
3
4
5
6
   KEY ROW_STATUS
------ ----------
     1 UPDATED
     3 UPDATED
     5 UPDATED
     7 UPDATED

# Reference Models

In this tutorial we explore the concept of reference models within the MODEL clause.

Until now the tutorials in this section have all dealt with a single model, called the main model. Beyond the main model you can create additional multi-dimensional arrays, called reference models, whose cells you can refer to in your main model. Reference models act like read-only lookup tables for your main model. As such, you cannot update or insert cells in a reference model.

Before we look at a reference model example note that, in addition to the table “T” we have used so far we will also use a table called “T3” which looks like this.

1
select * from T3;
1
2
3
4
5
KEY        NAME
---------- ---------------
a1         Alpha Squad 1
a2         Alpha Squad 2
a3         Alpha Squad 3

Now here is the reference model example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select
  key, group_2, group_2_name
from
  t
model
  REFERENCE T3_REF
    ON
    (
      SELECT KEY, NAME
      FROM   T3
    )
    DIMENSION BY ( KEY )
    MEASURES     ( NAME )
  main T_MAIN
    dimension by ( key )
    measures     ( group_2, 'Undefined Group' as group_2_name )
    rules        ( group_2_name[any] = T3_REF.NAME[ GROUP_2[CV()] ] )
order by
  key
;
1
2
3
4
5
6
7
8
9
10
11
12
   KEY GROUP_2    GROUP_2_NAME
------ ---------- ---------------
     1 a1         Alpha Squad 1
     2 a2         Alpha Squad 2
     3 a3         Alpha Squad 3
     4 a1         Alpha Squad 1
     5 a2         Alpha Squad 2
     6 a3         Alpha Squad 3
     7 a1         Alpha Squad 1
     8 a2         Alpha Squad 2
     9 a1         Alpha Squad 1
    10 a2         Alpha Squad 2

# Gotchas

If you try to update a reference model cell you will get an error.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  key, group_2, group_2_name
from
  t
model
  reference t3_ref
    on
    (
      select key, name
      from   t3
    )
    dimension by ( key )
    measures     ( name )
  main t_main
    dimension by ( key )
    measures     ( group_2, 'Undefined Group' as group_2_name )
    rules        ( T3_REF.NAME['a1'] = 'Alpha Squad 7' )
order by
  key
;
    rules        ( T3_REF.NAME['a1'] = 'Alpha Squad 7' )
                   *
ERROR at line 17:
ORA-00904: : invalid identifier

# Setup

Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
create table t
(
  key        number(3)    ,
  key_2      varchar2(5)  ,
  group_1    varchar2(7)  ,
  group_2    varchar2(7)  ,
  date_val   date         ,
  num_val    number
);

insert into t values ( 1 , 'T-1' , 'A' , 'a1' , date '2005-01-01', 100  );
insert into t values ( 2 , 'T-2' , 'A' , 'a2' , date '2005-06-12', 200  );
insert into t values ( 3 , 'T-3' , 'A' , 'a3' , null             , 300  );
insert into t values ( 4 , 'T-4' , 'B' , 'a1' , date '2006-02-01', null );
insert into t values ( 5 , 'T-5' , 'B' , 'a2' , date '2006-06-12', 300  );
insert into t values ( 6 , 'T-6' , 'B' , 'a3' , date '2005-01-01', 100  );
insert into t values ( 7 , 'T-7' , 'C' , 'a1' , date '2006-06-12', 100  );
insert into t values ( 8 , 'T-8' , 'C' , 'a2' , null             , null );
insert into t values ( 9 , 'T-9' , null, 'a1' , date '2005-02-01', 200  );
insert into t values ( 10, 'T-10', null, 'a2' , date '2005-02-01', 800  );

commit;

column group_1 format a10
column group_2 format a10
column num_val format 999999

create table t2
(
  key  number(3)
);

insert into t2 values ( 2 );
insert into t2 values ( 4 );
insert into t2 values ( 6 );


create table t3
(
  key  varchar2(10) ,
  name varchar2(15)
);

insert into t3 values ( 'a1', 'Alpha Squad 1' );
insert into t3 values ( 'a2', 'Alpha Squad 2' );
insert into t3 values ( 'a3', 'Alpha Squad 3' );

commit;

column key              format 99999
column new_text_measure format a20

column flag              format a4
column using_any         format a9
column using_is_any      format a12
column using_null        format a10
column using_is_null     format a13
column using_equals_null format a17

# Cleanup

Run the code on this page to drop the sample tables, procedures, etc. created in earlier parts of this section. To clear session state changes (e.g. those made by SET, COLUMN, and VARIABLE commands) exit your SQL*Plus session after running these cleanup commands.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

1
2
3
4
5
drop table t  ;
drop table t2 ;
drop table t3 ;

exit
Edited on