oracle Interval partitioning [message #410972] |
Wed, 01 July 2009 06:10  |
bgraupe
Messages: 25 Registered: July 2009 Location: Windhoek Namibia
|
Junior Member |
|
|
I am trying to create a table with interval partitioning.
The partition by range column (DIMDATEID) is a NUMBER, but actually contains a DATE value in the format YYYYMMDD.
My partition created for 01 July 2009 now show values less than 20090631 and not 20090701. I created the table as follows:CREATE TABLE FACT_XXX_TB
(
DIMDATEID NUMBER NOT NULL,
DIMTIMEID NUMBER NOT NULL,
BSC VARCHAR2(60) ,
CELL_ID NUMBER
)
partition BY range
(DIMDATEID)
interval
(1)
( partition P01_JAN08 VALUES less than (20090630)
TABLESPACE CDRGSM_NOV08 pctfree 0 initrans 1
maxtrans 255 STORAGE ( INITIAL 160K NEXT 1M
minextents 1 maxextents unlimited ) ); I tried the following as well:CREATE TABLE FACT_XXX_TB
(
DIMDATEID NUMBER NOT NULL,
DIMTIMEID NUMBER NOT NULL,
BSC VARCHAR2(60) ,
CELL_ID NUMBER
)
partition BY range
(DIMDATEID)
interval
(numtodsinterval(1,'DAY'))
( partition P01_JAN08 VALUES
less than (to_number(to_date('01-01-2005','dd-mm-yyyy')))
TABLESPACE CDRGSM_NOV08 pctfree 0 initrans 1
maxtrans 255 STORAGE ( INITIAL 160K NEXT 1M
minextents 1 maxextents unlimited ) ); but then I got the following error: ora 14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
Please assist.
Kind regards
Bonita
[Updated on: Wed, 01 July 2009 06:41] by Moderator Report message to a moderator
|
|
|
|
Re: oracle Interval partitioning [message #411017 is a reply to message #410983] |
Wed, 01 July 2009 08:35   |
bgraupe
Messages: 25 Registered: July 2009 Location: Windhoek Namibia
|
Junior Member |
|
|
Thank you for your reply.
Yes, I noted what you said, but do you perhaps know of a workaround to let oracle know I will increment with an interval of 1 DAY but in a number column?
Regards
|
|
|
Re: oracle Interval partitioning [message #411034 is a reply to message #411017] |
Wed, 01 July 2009 09:51   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
bgraupe wrote on Wed, 01 July 2009 15:35 | but do you perhaps know of a workaround to let oracle know I will increment with an interval of 1 DAY but in a number column?
|
That does not make sense to me. According to Oracle documentation here Quote: | This new partitioning strategy fully automates the partition creation for range. In other words, new partitions will be created when they are needed. By defining the interval criteria, the database knows when to create new partitions for new or modified data.
|
there is no need for Oracle to "know" how to "increment with an interval of 1 DAY but in a number column" - there is only need to let it know the (smallest) difference between two days - which is 1.
Of course, many possible partition ranges will not be created until row with wrong date representation (e.g. 20090632) will be inserted. But this is only consequence of the idea of storing DATE in the column with NUMBER data type, as there is no easy way to prohibit this.
|
|
|
Re: oracle Interval partitioning [message #411036 is a reply to message #411034] |
Wed, 01 July 2009 10:12  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
You are right:
SQB> CREATE TABLE FACT_XXX_TB
2 (
3 DIMDATEID NUMBER NOT NULL,
4 DIMTIMEID NUMBER NOT NULL,
5 BSC VARCHAR2(60) ,
6 CELL_ID NUMBER
7 )
8 partition BY range
9 (DIMDATEID)
10 interval
11 (1)
12 ( partition P01_JAN08 VALUES less than (20090630)
13 );
Table created.
SQB> select partition_name, high_value from user_tab_partitions where table_name='FACT_XXX_TB';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
P01_JAN08 20090630
1 row selected.
SQB> insert into FACT_XXX_TB(DIMDATEID , DIMTIMEID ) values (20090701,20090701);
1 row created.
SQB> select partition_name, high_value from user_tab_partitions where table_name='FACT_XXX_TB';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
P01_JAN08 20090630
SYS_P21 20090702
2 rows selected.
Of course, 20090630 data will come into a partition with a wrong (date) high value:
SQB> insert into FACT_XXX_TB(DIMDATEID , DIMTIMEID ) values (20090630,20090630);
1 row created.
SQB> select partition_name, high_value from user_tab_partitions where table_name='FACT_XXX_TB';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------
P01_JAN08 20090630
SYS_P21 20090702
SYS_P22 20090631
3 rows selected.
(And of course nothing prevent from inserting decimal (pseudo)date.
Regards
Michel
|
|
|