I have tried this earlier and it works:
- I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE
- Hence for the ID present in T1, fetch the DATE and STATE from T2
- Create new table T3 which is partitioned form of T1 on DATE key, sub-partitioned on STATE key
- Pull all records from T1 and insert every row in T3 with not just two additional columns DATE and STATE. But also in their respective partition/sub-partition
My data is stored like this..
I used following create statement to create my table T3
CREATE TABLE SLABELSVALUE
( "DOC_ID" VARCHAR2(80 BYTE) NOT NULL ENABLE,
...
...
"DOC_DATETIME" DATE NOT NULL ENABLE, //additional in T3 compared to T1
"DOC_STATE" VARCHAR2(5 BYTE), //additional in T3 compared to T1
CONSTRAINT...
PARTITION BY RANGE (DOC_DATETIME)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) **Line added for auto partition
SUBPARTITION BY LIST (DOC_STATE)
SUBPARTITION TEMPLATE(
SUBPARTITION IN_PB VALUES('IN-PB') , //All states
SUBPARTITION IN_RJ VALUES('IN-RJ') ,
...
)
(
PARTITION p0 VALUES LESS THAN (TO_DATE('01-MAY-2006','DD-MON-YYYY'))
//Ealier when it worked I defined all partitions for testing,
//When I included above lines `**` for auto partitioning it doesn't work
);
In my trial scenario, it worked when I did not include Interval partitioning mechanism.
I also defined all the partitions explicitly for testing.
- I have
T1data in production which reaches > 1 TB. - 1 Billion rows each month
- First data back in 2006..that old
Error Summary
What am I trying now?
I do not want to explicitly mention 10 years X 12(months) partitions up till this date. Hence included INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) and I do not want to define partitions explicitly for future months.
And defined first partition as per Oracle
What is wrong now?
ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions
Have I checked for NULL values
Yes, as other blogs suggest I checked for NULL values in my columns and there is none.
What I suspect?
With Interval partitioning, sub-partitions might not be supported.
The statement that produces the error is:
INSERT INTO T3
SELECT T1.*, T2.DOC_DATETIME, T2.DOC_STATE
FROM T1
LEFT OUTER JOIN T2
ON (T1.DOC_ID = T2.DOC_ID)
WHERE T2.DOC_DATETIME BETWEEN '01-APR-06' AND SYSDATE;
I am getting output if I remove the INSERT from line 1 and just keep SELECT.
Table structures:
T1 (Doc_ID varchar not null PK, label_ID varchar not null, label_val varchar not null)
T2 (DOc_ID varchar not null PK, Doc_State varchar not null, Doc_date DATE not null)
T3 (DOC_ID...,label_ID...,label_val..., Doc_State..., Doc_Date)
Please let me know if there is a solution.
