| Interval Partitioning and NULL Column Values [message #447239] |
Fri, 12 March 2010 10:49  |
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 #447248 is a reply to message #447246] |
Fri, 12 March 2010 12:22  |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
BlackSwan wrote on Fri, 12 March 2010 12:08When 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 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
John Watson wrote on Fri, 12 March 2010 19:09I 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)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
