Create a composite partition using a template in Oracle11g
Using a template to create a composite partition greatly reduces the complexity of the script. Here is an experiment:
-- Create a composite partition using the 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 ('1970-01-01 ', 'yyyy-mm-dd '))
(
Subpartition p_inclusub1 values ('sys '),
Subpartition p_partition sub3 values ('system '),
Subpartition p_partition sub5 values ('test '),
Subpartition p_1_sub7 values ('hr ')
),
Partition p_2 values less than (to_date ('1970-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 ('1970-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
-- Create a composite partition using the 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)
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 ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition p_2 values less than (to_date ('1970-01-01 ', 'yyyy-mm-dd ')),
Partition p_3 values less than (to_date ('1970-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