Home » SQL & PL/SQL » SQL & PL/SQL » Interval Partitioning and NULL Column Values (Oracle 11g 11.1.0.7.0 - HPUX 11.23)
Interval Partitioning and NULL Column Values [message #447239] Fri, 12 March 2010 10:49 Go to next message
chrisK17
Messages: 12
Registered: January 2008
Junior Member
Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.

If you create a range partitioned table; using interval partitioning, say something like this:

CREATE table range_parti (
    CODE NUMBER(5),
    DESCRIPTION VARCHAR2(50),
    CREATED_DATE DATE)
    PARTITION BY RANGE (created_date)
    INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);


but you try to insert a null value as the partition key, you get the following error:

SQL> INSERT INTO range_parti VALUES (1,'one',NULL);
INSERT INTO range_parti VALUES (1,'one',NULL)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Elapsed: 00:00:00.07


Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.

It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column. Has anyone had any luck with interval partitioning using NULLs?

Re: Interval Partitioning and NULL Column Values [message #447246 is a reply to message #447239] Fri, 12 March 2010 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
When all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2125922

For INTERVAL Clause
"You cannot specify NULL values for the partitioning key column."
Re: Interval Partitioning and NULL Column Values [message #447247 is a reply to message #447239] Fri, 12 March 2010 12:09 Go to previous messageGo to next message
John Watson
Messages: 6580
Registered: January 2010
Location: Global Village
Senior Member
I would say that this is the expected behaviour, because a null is the maxvalue, in this context. Can you use a default clause on the column, to make sure it isn't ever null?
Re: Interval Partitioning and NULL Column Values [message #447248 is a reply to message #447246] Fri, 12 March 2010 12:22 Go to previous messageGo to next message
chrisK17
Messages: 12
Registered: January 2008
Junior Member
BlackSwan wrote on Fri, 12 March 2010 12:08
When all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2125922

For INTERVAL Clause
"You cannot specify NULL values for the partitioning key column."


Gah, I can't believe I missed that. I was just looking at that page today. Thanks.

Quote:
I would say that this is the expected behaviour, because a null is the maxvalue, in this context. Can you use a default clause on the column, to make sure it isn't ever null?


For our first few target tables for partitioning, doing that extends the scope of the project dramatically. My goal here was just to find out if it was possible to account for NULLs with interval partitioning , but it appears it is not.

Thanks for the help everyone.
Re: Interval Partitioning and NULL Column Values [message #447296 is a reply to message #447247] Sat, 13 March 2010 03:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
John Watson wrote on Fri, 12 March 2010 19:09
I would say that this is the expected behaviour, because a null is the maxvalue, in this context. Can you use a default clause on the column, to make sure it isn't ever null?

A default clause on a column does not prevent it from being null! The default is only used if the column is not given an explicit value (which can be null)
Re: Interval Partitioning and NULL Column Values [message #447297 is a reply to message #447296] Sat, 13 March 2010 04:02 Go to previous message
John Watson
Messages: 6580
Registered: January 2010
Location: Global Village
Senior Member
I didn't know that! And I've just tested it, of course. Thanks for the correction.
Previous Topic: Procedure Insert Into
Next Topic: Problem with (seemingly) simple sql query
Goto Forum:
  


Current Time: Fri Dec 09 03:33:36 CST 2016

Total time taken to generate the page: 0.12701 seconds