Changing the subpartition template and pre-existing partitions
My colleague Tank could not insert some records into a subpartitioned table the other day as he kept getting an ORA-14400 error and couldn’t work out why – nor could I straight away until he mentioned the possibility that the subpartition template had changed on the table…which it had.
Lets try and work through the problem…first lets create some test tables…
DROP TABLE j4134_test1 PURGE / CREATE TABLE j4134_test1 ( col1 NUMBER NOT NULL ,col2 NUMBER NOT NULL ,col3 VARCHAR2(254) NOT NULL ) PARTITION BY RANGE (col1) SUBPARTITION BY LIST(col2) SUBPARTITION TEMPLATE (SUBPARTITION s1 VALUES (1), SUBPARTITION s2 VALUES (2), SUBPARTITION s3 VALUES (3) ) ( PARTITION p1 VALUES LESS THAN (1000) ,PARTITION p2 VALUES LESS THAN (2000) ,PARTITION p3 VALUES LESS THAN (3000) ,PARTITION p4 VALUES LESS THAN (4000) ) / ALTER TABLE j4134_test1 SET SUBPARTITION TEMPLATE (SUBPARTITION s1 VALUES (1), SUBPARTITION s2 VALUES (2) ) / ALTER TABLE j4134_test1 ADD PARTITION p5 VALUES LESS THAN(5000) / DROP TABLE j4134_test2 PURGE / CREATE TABLE j4134_test2 ( col1 NUMBER NOT NULL ,col2 NUMBER NOT NULL ,col3 VARCHAR2(254) NOT NULL ) PARTITION BY LIST (col2) ( PARTITION s1 VALUES (1) ,PARTITION s2 VALUES (2) ,PARTITION s3 VALUES (3) ) /
OK, that’s everything set up…now lets just have a look at what partitions and subpartitions we have:
ae_aml[522/313]@AED52> SELECT partition_name
2 , subpartition_name
3 FROM user_tab_subpartitions
4 WHERE table_name='J4134_TEST1'
5 /
Partition Sub Part
Name Name
-------------------- ------------
P1 P1_S1
P1 P1_S2
P1 P1_S3
P2 P2_S1
P2 P2_S2
It is a prescription drug thus applying prescription for the drug is adequate before starting off levitra sample its prescription dosage. This effective solution has tablets viagra been launched in market by the medical experts. Puss may appear from your skin. cialis 10 mg raindogscine.com In the cialis professional no prescription case of chronic ailments, a joint consultation is obtained from the experts. P2 P2_S3
P3 P3_S1
P3 P3_S2
P3 P3_S3
P4 P4_S1
P4 P4_S2
P4 P4_S3
P5 P5_S1
P5 P5_S2
14 rows selected.
Notice that Partition P5 has only two subpartitions whilst the other partitions all have three subpartitions.
Now lets do a couple of tests…
First lets try and replicate the original problem…
ae_aml[522/313]@AED52> SET ECHO ON ae_aml[522/313]@AED52> INSERT INTO j4134_test1(col1,col2,col3) 2 VALUES(3500,3,'TEST') 3 / 1 row created.
This worked – because the subpartition template used for the Partition P4 where the COL1 value 3500 would be inserted, included a subpartition for COL2 with values of 3 – no problem.
Lets try again…
ae_aml[522/313]@AED52> INSERT INTO j4134_test1(col1,col2,col3) 2 VALUES(4500,3,'TEST') 3 / INSERT INTO j4134_test1(col1,col2,col3) * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
Aha – now it fails…because the COL1 value of 4500 would result in Partition P5 being used – but this was created after the subpartition template was changed to only have subpartitions for values 1 and 2 – the value of 3 for COL2 does not have a “home” to go to, so the statement fails.
This is interesting because it means that Oracle allows us to have Partitions setup with different subpartition layouts under them depending on what the template was at the time of partition creation.
When I first thought about this I figured it wasn’t possible/correct to do this and that the subpartitions had to be uniform across the partitions, but when you think about that, it would make life difficult to change the template – what should Oracle do with the “missing” or “extra” or “modified”
subpartitions in the pre-existing partitions? Create/drop/modify them? Ignore them? If it tried to create missing ones there would be all sorts of questions like which tablespace to use and what storage characteristics to set.
As the documentation says, it leaves what exists already as is and just uses the new template for partitions created in the future…which brings me to another point…that piece of code I put up on this post needed updating because I didn’t know about this subpartition template issue…now the code will check to ensure that if we are exchanging a partitioned table for a partition of a composite partitioned table that we need to ensure the source table is partitioned in the same way
(LIST/HASH) as the subpartitioning on the target partition and that there are the same number of partitions in the source table and the target table partition – if not the process fails…like this:
ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p1 WITH TABLE j4134_test2
2 /
Table altered.
ae_aml[522/313]@AED52> REM Now put it back...
ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p1 WITH TABLE j4134_test2
2 /
Table altered.
ae_aml[522/313]@AED52> REM now try with the mismatched one - fails!
ae_aml[522/313]@AED52> ALTER TABLE j4134_test1 EXCHANGE PARTITION p5 WITH TABLE j4134_test2
2 /
ALTER TABLE j4134_test1 EXCHANGE PARTITION p5 WITH TABLE j4134_test2
* ERROR at line 1:
ORA-14294: Number of partitions does not match number of subpartitions
I’ve updated the code to check for this scenario and it now reports it.