3

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

enter image description here

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

2

I had to explicitly define all my range partitions. Interval partitioning did not work well with sub-partitioning template.

Oracle does HARD Limit of 1m partitions to my interval partitions.

I defined partitions as

PARTITION jan06 VALUES LESS THAN (TO_DATE('01-FEB-2006','DD-MON-YYYY')),
...
ALL THE WAY FOR NEXT 20 YEARS..
...
PARTITION jan26 VALUES LESS THAN (TO_DATE('01-FEB-2026','DD-MON-YYYY')),

Refer here and there

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.