Home » SQL & PL/SQL » SQL & PL/SQL » oracle Interval partitioning (Oracle 11g)
oracle Interval partitioning [message #410972] Wed, 01 July 2009 06:10 Go to next message
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 #410983 is a reply to message #410972] Wed, 01 July 2009 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
NUMBER is NUMBER.
DATE is DATE.

After number 20090631 is number 20090632.
After date 20090631 is date 20090701.

How Oracle knows the difference is datatype.
Oracle does not care about what you've in mind.

Regards
Michel
Re: oracle Interval partitioning [message #411017 is a reply to message #410983] Wed, 01 July 2009 08:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Problem with types of arguments
Next Topic: partition tables
Goto Forum:
  


Current Time: Sat Feb 08 20:53:22 CST 2025