I am using TD 13.I have an erro 5728 with partition violation.
I have a table with follwoing structure
CREATE TABLE T1
COL2 DATE FORMAT 'YYYY-MM-DD',
PARTITION BY CAST(COL2 AS INTEGER);
While I am inserting the data from select logic into this table.I am getting partition violation because the values allowed in the partition from 1 to 65535.the dates being converted into integer format is >65535 value.
We constructed this table as per requirement.How far this is correct by puttin cast(col2 as integer) in partition by.
Is there any approach to do this.
I thought to have partition by range_n.How do you suggest?
Good that you know the reason for getting the error!
Having a partition defined on the integer value of dates doesn't seem logical to me. Normaly the tables are partitioned based on the intervals like MONTH or DAY, and it is always preferred to have the paritions that have data and avoid the empty partitions.
I think you should try to use RANGE_N and define the partitions on DAY or MONTH interval, and if you analyze the data properly, you won't have partitions more than 65K.
Create table T1(...)
PRIMARY INDEX (col1)
PARTITION BY( RANGE_N (col2 between '2012-01-01' and '2012-12-31' each interval '1' MONTH))