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