Oracle11g下用模板建复合分区

Oracle11g下用模板建复合分区

利用模板建复合分区大大的降低了脚本的复杂度，下面来做个试验：

--未使用模板的方法建复合分区

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

--使用模板的方法建复合分区
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