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?

Dec 1 '05 #1

Subscribe Reply

3947

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

Dec 2 '05 #2

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

Dec 2 '05 #3

rajesh...

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

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
"parameteri ze" 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

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 ?

Dec 2 '05 #6

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

Dec 2 '05 #7

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

Dec 2 '05 #8

Similar topics

3383

Cannot Get Field Nullable Property

by: Paul Eaton | last post by:

Hi I am using asp/vbscript/ado/mssql. I am able to get the nullable property OK when generating a recordset with a simple SQL statement such as "select Fld1,Fld2 from Table1" and then looping thro' the fields and :- Response.Write(rs.fields(x).attributes and adFldIsNullable)

ASP / Active Server Pages

4123

Table partitioning and Rules

by: Girish | last post by:

I have a set of tables partitioned horizontally. DML below. I have also created a view that will allow me to display information stored in all partitioned tables. Im trying to create an INSERT RULE on the VIEW that would direct an insert into the appropriate partition table depending on the personal LastName. Can anyone help me with this? Thanks, Girish

PostgreSQL Database

7265

Partitioning In DB2

by: Jane | last post by:

In Oracle we can partition a table as follows. What is the equivalent in DB2? CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) (

DB2 Database

5950

Partition function - Unknow range column

by: sameer_deshpande | last post by:

Hi, I need to create a partition table but the column on which I need to create a partition may not have any logical ranges. So while creating or defining partition function I can not use any range. like CREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FOR VALUES (1,100,1000);

Microsoft SQL Server

51672

Adding a nullable datetime column to a datatable does not work

by: GG | last post by:

I am trying to add a nullable datetime column to a datatable fails. I am getting exception DataSet does not support System.Nullable<>. None of these works dtSearchFromData.Columns.Add( new DataColumn( "StartDate", typeof( DateTime? ) ) ); dtSearchFromData.Columns.Add( new DataColumn( "EndDate", typeof( System.Nullable<DateTime>) ) ); Any ideas?

C# / C Sharp

1999

Error : partitioning column was not found ( Partitioning View)

by: harrylarenson | last post by:

Hi, Happy New Year. I am trying to insert a query to a partitioned view but the error is : Server: Msg 4436, Level 16, State 12, Line 1 UNION ALL view 'T' is not updatable because a partitioning column was not found. Here is all my query statements : create table t1 (ID int primary key, Code int check(code between 1 and 15 ))

MySQL Database

1880

UDB Range Partitioning in V8: Using Ranges in Constraints

by: Nate Eaton | last post by:

According to the original whitepaper on UDB range partitioning (http:// www-106.ibm.com/developerworks/db2/library/techarticle/0202zuzarte/ 0202zuzarte.pdf), you can use a range as a criteria, either in the UNION ALL view or in constraints. One restriction it lists, though, is that the optimizer can't use a constraint that references a range versus a discrete value for SQL containing host variables or parameter markers. That was for V7 and...

DB2 Database

11325

Help on Partitioning column was not found.

by: Sonny | last post by:

Hi, I don't know if I missed anything. I have 2 member tables and one partition view in SQL 2000 defined as following CREATE VIEW Server1.dbo.UTable AS SELECT * FROM Server1..pTable1 UNION ALL

Microsoft SQL Server

3688

SQLServer Table Partitioning

by: Piero 'Giops' Giorgi | last post by:

Hi! I have a question: I already have a DB that uses partitions to divide data in US Counties, partitioned by state. Can I use TWO levels of partitioning? I mean... 3077 filegroups and 50 partition functions that address

Microsoft SQL Server

9621

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10267

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10040

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8939

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7463

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6717

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5355

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4012

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3611

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP