469,578 Members | 1,914 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,578 developers. It's quick & easy.

Nullable Vs Not Nullable Column for Partitioning a Union ALL View

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?

Dec 1 '05 #1
7 3838
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
Dec 2 '05 #2
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


Dec 2 '05 #3
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


Dec 2 '05 #4
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
Dec 2 '05 #5
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 ?
Dec 2 '05 #6
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
Dec 2 '05 #7
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

Dec 2 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Girish | last post: by
7 posts views Thread by Jane | last post: by
5 posts views Thread by sameer_deshpande | last post: by
15 posts views Thread by Piero 'Giops' Giorgi | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.