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.

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 ;

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 ;

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."

© 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.

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 ;

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.

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 ;

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