您好,登录后才能下订单哦!
11G分区表自动分区
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分区。
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命名子分区。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。