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