SQL Snippets ™: Tutorials for Oracle Developers

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.

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
;
 

   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.

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
;
 

   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.

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
;
 

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.

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
;
 

   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 M_2. It is not related to the row in table T with a KEY value of '5'.