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
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
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
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.