11G分區(qū)表自動(dòng)分區(qū)
成都創(chuàng)新互聯(lián)專注于綏棱企業(yè)網(wǎng)站建設(shè),自適應(yīng)網(wǎng)站建設(shè),商城網(wǎng)站制作。綏棱網(wǎng)站建設(shè)公司,為綏棱等地區(qū)提供建站服務(wù)。全流程按需搭建網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)
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')
插入測(cè)試數(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
插入測(cè)試數(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ū)
說(shuō)明：對(duì)于interval分區(qū)表插入“不存在分區(qū)”對(duì)應(yīng)的數(shù)值時(shí)，會(huì)自動(dòng)生成按照interval生成相應(yīng)分區(qū)；若先插入較大數(shù)值，再插入較小數(shù)值，分區(qū)會(huì)按照interval依次生成，如test_range只存在20160612分區(qū)，插入20160615數(shù)值時(shí)會(huì)生成20160615分區(qū)，再插入20160614數(shù)值時(shí)會(huì)再生成20160614分區(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')));
插入測(cè)試數(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
說(shuō)明：同時(shí)使用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ū)。
重慶分公司
重慶分公司
