Using templates to build composite partitions under Oracle 11g

Using a template to build a composite partition greatly reduces the complexity of the script, the following is an experiment:

--Building a composite partition without using a template method

sql> drop table test purge;

Sql> CREATE TABLE Test
(
Owner VARCHAR2 (10),
Created date,
object_name VARCHAR2 (10),
Object_type VARCHAR2 (20)
)
Partition by range (created) Subpartition by list (owner)
(
Partition p_1 values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')
(
Subpartition p_1_sub1 values (' sys '),
Subpartition p_1_sub3 values (' system '),
Subpartition p_1_sub5 values (' Test '),
Subpartition p_1_sub7 values (' hr ')
),
Partition p_2 values less than (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')
(
Subpartition p_2_sub1 values (' sys '),
Subpartition p_2_sub3 values (' system '),
Subpartition p_2_sub5 values (' Test '),
Subpartition p_2_sub7 values (' hr ')
),
Partition P_3 values less than (to_date (' 2014-01-01 ', ' yyyy-mm-dd ')
(
Subpartition p_3_sub1 values (' sys '),
Subpartition p_3_sub3 values (' system '),
Subpartition p_3_sub5 values (' Test '),
Subpartition p_3_sub7 values (' hr ')
),
Partition P_other values less than (MaxValue)
(
Subpartition p_other_sub1 values (' sys '),
Subpartition p_other_sub3 values (' system '),
Subpartition p_other_sub5 values (' Test '),
Subpartition p_other1_sub7 values (' hr ')
)
);
Sql> Select S.partition_name
From User_tab_partitions S
where s.table_name = ' TEST ';
Partition_name
------------------------------
P_1
P_2
P_3
P_other

sql> Col partition_name format A10
Sql> Select S.partition_name, S.subpartition_name
From User_tab_subpartitions S
where s.table_name = ' TEST ';
Partition_ Subpartition_name
---------- ------------------------------
P_1 P_1_sub1
P_1 P_1_SUB3
P_1 P_1_SUB5
P_1 P_1_sub7
P_2 P_2_sub1
P_2 P_2_SUB3
P_2 P_2_SUB5
P_2 P_2_sub7
P_3 P_3_sub1
P_3 P_3_SUB3
P_3 P_3_SUB5
P_3 P_3_sub7
P_other P_other_sub1
P_other P_OTHER_SUB3
P_other P_OTHER_SUB5
P_other P_other1_sub7

--Use template method to build composite partition
sql> drop table test purge;
Sql> CREATE TABLE Test
(
Owner VARCHAR2 (10),
Created date,
object_name VARCHAR2 (10),
Object_type VARCHAR2 (20)
)
Partition by range (created) Subpartition by list (owner)
subpartition Template
(
Subpartition sub1 values (' sys '),
Subpartition sub3 values (' system '),
Subpartition sub5 values (' Test '),
Subpartition sub7 values (' hr ')
)
(
Partition p_1 values less than (to_date (' 2012-01-01 ', ' yyyy-mm-dd ')),
Partition p_2 values less than (to_date (' 2013-01-01 ', ' yyyy-mm-dd ')),
Partition P_3 values less than (to_date (' 2014-01-01 ', ' yyyy-mm-dd ')),
Partition P_other values less than (MaxValue)
);


Sql> Select S.partition_name
From User_tab_partitions S
where s.table_name = ' TEST ';
Partition_
----------
P_1
P_2
P_3
P_other


Sql> Select S.partition_name, S.subpartition_name
From User_tab_subpartitions S
where s.table_name = ' TEST ';
Partition_ Subpartition_name
---------- ------------------------------
P_1 P_1_sub1
P_1 P_1_SUB3
P_1 P_1_SUB5
P_1 P_1_sub7
P_2 P_2_sub1
P_2 P_2_SUB3
P_2 P_2_SUB5
P_2 P_2_sub7
P_3 P_3_sub1
P_3 P_3_SUB3
P_3 P_3_SUB5
P_3 P_3_sub7
P_other P_other_sub1
P_other P_OTHER_SUB3
P_other P_OTHER_SUB5
P_other P_other_sub7

Using templates to build composite partitions under Oracle 11g