Nullable Vs Not Nullable Column for Partitioning a Union ALL View

[Rajesh..............................]

What is the impact of using a nullable column vs a not nullable column
for partitioning a Union ALL View? I have a Union ALL View with ten
underlying tables unioned based on different values for a column
partition_id in these tables. Now, if I define this column as NOT NULL,
I notice that the loading this view is almost twice fast as if I had
defined it NULLABLE. Can anyone explain? What exactly is DB2 doing in
each of the case when inserting a row into the view?

[Serge Rielau]

Rajesh.............................. wrote:

What is the impact of using a nullable column vs a not nullable column
for partitioning a Union ALL View? I have a Union ALL View with ten
underlying tables unioned based on different values for a column
partition_id in these tables. Now, if I define this column as NOT NULL,
I notice that the loading this view is almost twice fast as if I had
defined it NULLABLE. Can anyone explain? What exactly is DB2 doing in
each of the case when inserting a row into the view?

Check out my article on developer works:
www.ibm.com serach for "Rielau"

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[rajesh...]

Serge, I have read your articles before. Which one exactly you are
referring to here? I havent found enough evidence to support my
finding. Are you agreeing that not null v. nullable can degrade
performance as much as 2 times for inserting the same data?

Thanks.

Serge Rielau wrote:

Rajesh.............................. wrote:

What is the impact of using a nullable column vs a not nullable column
for partitioning a Union ALL View? I have a Union ALL View with ten
underlying tables unioned based on different values for a column
partition_id in these tables. Now, if I define this column as NOT NULL,
I notice that the loading this view is almost twice fast as if I had
defined it NULLABLE. Can anyone explain? What exactly is DB2 doing in
each of the case when inserting a row into the view?

Check out my article on developer works:
www.ibm.com serach for "Rielau"

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[rajesh...]

Serge, I have read your articles before. Which one exactly you are
referring to here? I havent found enough evidence to support my
finding. Are you agreeing that not null v. nullable can degrade
performance as much as 2 times for inserting the same data?

Thanks.

Serge Rielau wrote:

Rajesh.............................. wrote:

What is the impact of using a nullable column vs a not nullable column
for partitioning a Union ALL View? I have a Union ALL View with ten
underlying tables unioned based on different values for a column
partition_id in these tables. Now, if I define this column as NOT NULL,
I notice that the loading this view is almost twice fast as if I had
defined it NULLABLE. Can anyone explain? What exactly is DB2 doing in
each of the case when inserting a row into the view?

Check out my article on developer works:
www.ibm.com serach for "Rielau"

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

rajesh... wrote:

Serge, I have read your articles before. Which one exactly you are
referring to here? I havent found enough evidence to support my
finding. Are you agreeing that not null v. nullable can degrade
performance as much as 2 times for inserting the same data?

I have some thoughts....
Insert through UNION ALL depends on check constraints.
Note that a check constraint of the form: (c1 BETWEEN 1 AND 5) will
permit NULL to be inserted for c1.
That is with a NULLable column
T1: (c1 BETWEEN 1 AND 6)
T2: (c1 BETWEEN 7 AND 10)
does not provide a guaranteed partitioning.
That means DB2 has to "do it the hard way" (as described in my article).
It needs to test all tables and count the number of successes.
If DB2 can prove the check constraints partition it can either
"parameterize" the INSERT (that is use a normal INSERT into a single
table template - you will find that the plan looses all but one table)
or at least it can do runtime elimination.
So there are really "three gears" to INSERT through UNION ALL.
Feel free to get explains (db2exfmt preferred) and I shoudl be able to
tell you what happens.

It may be that all you need is to improve your check constraints to tell
DB2 exactly where NULLs are supposed to end up:
T1: (c1 BETWEEN 2 AND 6 AND c1 IS NOT NULL)
T2: (c1 BETWEEN 7 AND 10 AND c1 IS NOT NULL)
T3: (c1 IS NULL)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

It may be that all you need is to improve your check constraints to tell DB2 exactly where NULLs are supposed to end up: T1: (c1 BETWEEN 2 AND 6 AND c1 IS NOT NULL)
T2: (c1 BETWEEN 7 AND 10 AND c1 IS NOT NULL)
T3: (c1 IS NULL)

Hmm - but presumably C1 will not stay null ? Otherwise why store it ?

[Serge Rielau]

Mark Townsend wrote:

Serge Rielau wrote:

> It may be that all you need is to improve your check constraints to

tell DB2 exactly where NULLs are supposed to end up:

> T1: (c1 BETWEEN 2 AND 6 AND c1 IS NOT NULL)
> T2: (c1 BETWEEN 7 AND 10 AND c1 IS NOT NULL)
> T3: (c1 IS NULL)

Hmm - but presumably C1 will not stay null ? Otherwise why store it ?

Not the most likely scenario presumably...
But I suppose if I used range partitioning my name in, say identity
management, then perhaps identities which I have not yet associated with
a name would go into some sort of NULL bucket.... (?)
The misc-partition so to speak :-)

Let's see what the OP has to say...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[rajesh...]

Actually my check constraints allow no ranges. Its quite simple, like:
T0: c1 = 0
T1: c1 = 1
and so on....
T9: c1 = 9
Note, there is no provision for null because the app ensures a null
wont come in for that column. I hope my explain plan in the follwing
format is helpful.

With C1 defined as NOT NULL in the DDL:
EST_ROWS TOTAL_COST OPERATION
---------------------------------------------------
- 25 0.RETURN
1.00 25 1.INSERT
1.00 0 2.TBSCAN: GENROW

With C1 not defined as NOT NULL in DDL:
EST_ROWS TOTAL_COST OPERATION
--------------------------------------------
- 250 0.RETURN
0.58 250 1.INSERT
0.58 225 2.INSERT
0.58 200 3.INSERT
0.58 175 4.INSERT
0.58 150 5.INSERT
0.58 125 6.INSERT
0.58 100 7.INSERT
0.58 75 8.INSERT
0.58 50 9.INSERT
0.58 25 10.INSERT
0.58 0 11.FILTER
0.61 0 12.TBSCAN
0.61 0 13.SORT
0.61 0 14.NLJOIN
1.00 0 15.TBSCAN:
GENROW
0.61 0 15.TBSCAN:
GENROW