Nullable Vs Not Nullable Column for Partitioning a Union ALL View

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rajesh.............................. - 01 Dec 2005 22:31 GMT

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?

Reply to this Message

Serge Rielau - 02 Dec 2005 01:00 GMT

> 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
[quoted text clipped - 3 lines]
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

rajesh... - 02 Dec 2005 01:49 GMT

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.

> > 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
[quoted text clipped - 9 lines]
> Cheers
> Serge

Reply to this Message

rajesh... - 02 Dec 2005 01:49 GMT

> > 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
[quoted text clipped - 9 lines]
> Cheers
> Serge

Reply to this Message

Serge Rielau - 02 Dec 2005 04:18 GMT

> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Mark Townsend - 02 Dec 2005 05:56 GMT

> 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 ?

Reply to this Message

Serge Rielau - 02 Dec 2005 06:15 GMT

> > It may be that all you need is to improve your check constraints to
> tell DB2 exactly where NULLs are supposed to end up:
[quoted text clipped - 3 lines]
>
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

rajesh... - 02 Dec 2005 17:32 GMT

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

Reply to this Message