11G自动分区interval&template
11G分区表自动分区
创建interval分区表
create table test_range (idnumber,test_date date)
partition by range(test_date)interval(numtodsinterval(1,'day'))
(partition p_20160612 values less than(to_date('20160613','yyyymmdd')));
SQL> selecttable_name,partitioning_type,partition_count,interval from user_part_tableswhere table_name='TEST_RANGE';
TABLE_NAME PARTITION PARTITION_COUNT INTERVAL
-------------------- ------------------------ ---------------------------------------------
TEST_RANGE RANGE 1048575（1024k同10G）NUMTODSINTERVAL(1,'DAY')
插入测试数据（存在分区）
SQL> insert into TEST_RANGE values(1,to_date('20160612','yyyymmdd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME PARTITION_NAME
--------------------------------------------------
TEST_RANGE P_20160612
插入测试数据（不存在分区）
SQL> insert into TEST_RANGE values(1,to_date('20160613','yyyymmdd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME PARTITION_NAME
--------------------------------------------------
TEST_RANGE P_20160612
TEST_RANGE SYS_P122
SQL> insert into TEST_RANGE values (1,to_date('20160615','yyyymmdd')); --先插入较大数值
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME PARTITION_NAME
--------------------------------------------------
TEST_RANGE P_20160612
TEST_RANGE SYS_P122
TEST_RANGE SYS_P123 --新增分区
SQL> insert into TEST_RANGE values (1,to_date('20160614','yyyymmdd')); --先插入中间数值
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,partition_namefrom user_tab_partitions where table_name='TEST_RANGE';
TABLE_NAME PARTITION_NAME
--------------------------------------------------
TEST_RANGE P_20160612
TEST_RANGE SYS_P122
TEST_RANGE SYS_P123
TEST_RANGE SYS_P124 --新增分区
说明：对于interval分区表插入"不存在分区"对应的数值时，会自动生成按照interval生成相应分区；若先插入较大数值，再插入较小数值，分区会按照interval依次生成，如test_range只存在20160612分区，插入20160615数值时会生成20160615分区，再插入20160614数值时会再生成20160614分区。
创建template分区表
drop table test_range purge;
create table test_range (idnumber,test_date date)
partition by range(test_date) interval(numtodsinterval(1,'day'))
subpartition by hash(id)
subpartition template
(subpartition a,
subpartition b,
subpartition c)
(partitionp_20160612 values less than (to_date('20160613','yyyymmdd')));
插入测试数据
SQL> insert into test_rangevalues(1,sysdate+2);
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';
TABLE_NAME SUBPARTITION_NAME
------------------------------------------------------------
TEST_RANGE P_20160612_A
TEST_RANGE P_20160612_B
TEST_RANGE P_20160612_C
TEST_RANGE SYS_SUBP125
TEST_RANGE SYS_SUBP126
TEST_RANGE SYS_SUBP127
发现新生成的分区并未按照template形式
SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));
alter table test_range add partition P_20160615values less than(to_date('20160616','yyyymmdd'))
*
ERROR at line 1:
ORA-14760: ADDPARTITION is not permitted on Interval partitioned objects
采取interval keyword创建的分区表不支持自己add partition
不采取interval创建template分区表
drop table test_range purge;
create table test_range (idnumber,test_date date)
partition by range(test_date)
subpartition by hash(id)
subpartition template
(subpartition a,
subpartition b,
subpartition c)
(partitionp_20160612 values less than (to_date('20160613','yyyymmdd')));
添加分区
SQL> alter table test_range addpartition P_20160615 values less than(to_date('20160616','yyyymmdd'));
Table altered.
SQL>
SQL> select table_name,subpartition_namefrom user_tab_subpartitions where table_name ='TEST_RANGE';
TABLE_NAME SUBPARTITION_NAME
------------------------------------------------------------
TEST_RANGE P_20160612_A
TEST_RANGE P_20160612_B
TEST_RANGE P_20160612_C
TEST_RANGE P_20160615_A
TEST_RANGE P_20160615_B
TEST_RANGE P_20160615_C
说明：同时使用partition interval & subpartition template关键字创建的分区表，子分区按照系统自定义命名子分区名字，不按照subpartition template命名子分区，并且不支持自己添加分区；仅使用subpartition template关键字创建的分区表，子分区按照subpartitiontemplate命名子分区。
