11G自動分區(qū)interval&template

11G分區(qū)表自動分區(qū)

創(chuàng)建interval分區(qū)表

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')

插入測試數(shù)據(jù)（存在分區(qū)）

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

插入測試數(shù)據(jù)（不存在分區(qū)）

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')); --先插入較大數(shù)值

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   --新增分區(qū)

SQL> insert into TEST_RANGE values (1,to_date('20160614','yyyymmdd')); --先插入中間數(shù)值

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  --新增分區(qū)

說明：對于interval分區(qū)表插入“不存在分區(qū)”對應(yīng)的數(shù)值時，會自動生成按照interval生成相應(yīng)分區(qū)；若先插入較大數(shù)值，再插入較小數(shù)值，分區(qū)會按照interval依次生成，如test_range只存在20160612分區(qū)，插入20160615數(shù)值時會生成20160615分區(qū)，再插入20160614數(shù)值時會再生成20160614分區(qū)。

創(chuàng)建template分區(qū)表

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')));

插入測試數(shù)據(jù)

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

發(fā)現(xiàn)新生成的分區(qū)并未按照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創(chuàng)建的分區(qū)表不支持自己add partition

不采取interval創(chuàng)建template分區(qū)表

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')));

添加分區(qū)

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關(guān)鍵字創(chuàng)建的分區(qū)表，子分區(qū)按照系統(tǒng)自定義命名子分區(qū)名字，不按照subpartition template命名子分區(qū)，并且不支持自己添加分區(qū)；僅使用subpartition template關(guān)鍵字創(chuàng)建的分區(qū)表，子分區(qū)按照subpartitiontemplate命名子分區(qū)。