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命名子分区。