Oracle 12c New Feature: Partition增强(四) multi-column list, auto-list ,interval subpartiti...

2017-01-05 11:05:30来源:作者:ANBOB人点击

继续我的12c partition系列, 查看之前文章请关注我BLOG: anbob.com和微信公众号:anbob手记, 这篇是12c partition多个新特性的集合, 看完会由衷的赞叹ORACLE rdbms在分区中所做的改进.

主要新特性有:

1, 支持多列的list partition

2, 自动list partition

3, 分区级的read only

4, interval subpartition

5, 自动list + interval subpartition 近乎全自动

以下所有测试使用的版本是:

SQL> select * from v$version;BANNER CON_ID-------------------------------------------------------------------------------- ----------Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta 0PL/SQL Release 12.2.0.0.1 - Beta 0CORE 12.2.0.0.0 Beta 0TNS for Linux: Version 12.2.0.0.0 - Beta 0NLSRTL Version 12.2.0.0.0 - Beta 0

— demo —

1, 多列(multi-column) list partitioning

在12c r2之前list分区只支持1列, 无法满足一些数据存储模型的拆分, 这个新特性引入list也可以像range一样在一个维度指定多列的分区表或子分区表. 目前最多支持 16个keys列, 同时也支持12c新特性的外部分区表和Reference partition及12c新引入auto-list特性的分区表.

# 11g r2SQL> create table anbob_t4( 2 id int, 3 name varchar2(20), 4 region varchar2(10), 5 cycle varchar2(10) 6 ) 7 partition by list(region,cycle) 8 ( 9 partition p1 values('010',2016), 10 partition p1 values('020',2016), 11 partition p1 values('0311',2016) 12 );(*ERROR at line 8:ORA-14304: List partitioning method expects a single partitioning column# 12c r2SQL> create table anbob_t4( id int, name varchar2(20), region varchar2(10), cycle varchar2(10) ) partition by list(region,cycle) ( partition p1 values('010',2016), partition p2 values('020',2016), partition p3 values('0311',2016) );Table created.SQL> insert into anbob_t4 values(1,'anbob','0311',2016);1 row created.SQL> @tabpart anbob_t4TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE_RAW COMPRESS INDE INMEMORY---------- ----------- --- --- -------------- ------------------ -------------------- -------- ---- --------ANBOB ANBOB_T4 1 NO P1 0 ( '010', '2016' ) DISABLED ON DISABLEDANBOB ANBOB_T4 2 NO P2 0 ( '020', '2016' ) DISABLED ON DISABLEDANBOB ANBOB_T4 3 NO P3 0 ( '0311', '2016' ) DISABLED ON DISABLEDSQL> select PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,AUTOLIST,DEF_READ_ONLY from user_part_tables where table_name='ANBOB_T4';PARTITION PARTITIONING_KEY_COUNT AUT DEF--------- ---------------------- --- ---LIST 2 NO NOSQL> @partkeys anbob_t4PARTK OWNER NAME COLUMN_NAME COLUMN_POSITION----- ------- - --------- - ------------ - ---------------TABLE ANBOB ANBOB_T4 REGION 1TABLE ANBOB ANBOB_T4 CYCLE 2

Note:

在12.2版本中创建了2个keys列的list分区, 上面的列子实现了区域与帐期两列上的分区. subpartition同样支持,不再演示.

2, 自动list partition

在12.2之前的版本list分区如果指定的值不存在并且default值的分区不存在会导致事务失败, 如果list的key值较多创建分区的维护量也较大, 在12.2中引入了新特性,如果list分区key不存在,在insert时可以打开该特性, oracle会自动的创建该key 的list分区. 前提条件是不能有default分区这很好理解, 只不过分区名和之前的interval分区一样是系统生成的, 该特性可以打开和关闭, 默认关闭.

接着上面创建的表

# 如果list不存在时, auto offSQL> insert into anbob_t4 values(1,'anbob','0311',2017);insert into anbob_t4 values(1,'anbob','0311',2017) *ERROR at line 1:ORA-14400: inserted partition key does not map to any partition# 打开自动SQL> alter table anbob_t4 set partitioning automatic;Table altered.SQL> insert into anbob_t4 values(1,'anbob','0311',2017);1 row created.SQL> commit;Commit complete.SQL> select PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,AUTOLIST,DEF_READ_ONLY from user_part_tables where table_name='ANBOB_T4';PARTITION PARTITIONING_KEY_COUNT AUT DEF--------- ---------------------- --- ---LIST 2 YES NOSQL> @tabpart ANBOB_T4TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW COMPRESS INDE INMEMORY---------- ------------ ----- --- -------------------- ---------- ------------------ -------------------- -------- ---- --------ANBOB ANBOB_T4 1 NO P1 0 ( '010', '2016' ) DISABLED ON DISABLEDANBOB ANBOB_T4 2 NO P2 0 ( '020', '2016' ) DISABLED ON DISABLEDANBOB ANBOB_T4 3 NO P3 0 ( '0311', '2016' ) DISABLED ON DISABLEDANBOB ANBOB_T4 4 NO SYS_P1558 0 ( '0311', '2017' ) DISABLED ON DISABLED# 关闭该特性SQL> alter table anbob_t4 set partitioning manual;Table altered.

3, 分区级的read only

在之前的版本中配置table的只读属性只能是表级, 12.2可以在分区或子分区级修改只读属性.

SQL> alter table anbob_t4 read only;Table altered.SQL> select PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,AUTOLIST,DEF_READ_ONLY from user_part_tables where table_name='ANBOB_T4';PARTITION PARTITIONING_KEY_COUNT AUT DEF--------- ---------------------- --- ---LIST 2 YES YESSQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,READ_ONLY from user_tab_partitions where table_name='ANBOB_T4';TABLE_NAME PARTITION_NAME HIGH_VALUE READ-------------------- -------------------- ------------------------------ ----ANBOB_T4 P1 ( '010', '2016' ) YESANBOB_T4 P2 ( '020', '2016' ) YESANBOB_T4 P3 ( '0311', '2016' ) YESANBOB_T4 SYS_P1558 ( '0311', '2017' ) YESSQL> alter table anbob_t4 modify partition p3 read write;Table altered.SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,READ_ONLY from user_tab_partitions where table_name='ANBOB_T4';TABLE_NAME PARTITION_NAME HIGH_VALUE READ-------------------- -------------------- ------------------------------ ----ANBOB_T4 P1 ( '010', '2016' ) YESANBOB_T4 P2 ( '020', '2016' ) YESANBOB_T4 P3 ( '0311', '2016' ) NOANBOB_T4 SYS_P1558 ( '0311', '2017' ) YESSQL> insert into anbob_t4 values(1,'weejar','0311',2016);1 row created.SQL> commit;Commit complete.SQL> insert into anbob_t4 values(1,'weejar','0311',2017);insert into anbob_t4 values(1,'weejar','0311',2017) *ERROR at line 1:ORA-14466: Data in a read-only partition or subpartition cannot be modified.-- subpartition levelSQL> alter table anbob_t5 modify subpartition SYS_SUBP1589 read only;Table altered.

Note:

可以看到分区级可以单独配置自己的只读属性. 分区级覆盖表级, 同样也可以是subpartition级

4, interval sub partition

在11g中引入的interval partition可以在date或number类型的列上有系统自动的创建固定步长的分区, 在12.2中引入了interval sub partition. 该特性的条件是:不能有MAX value,不会手动add分区, 子分区的模板是固定的, 一个表最多有1百万subpartition(可以是一个1partition下100万subpartition,也可以是100万partition下1个subpartition).

SQL> create table anbob_t5( 2 id int, 3 name varchar2(20), 4 region varchar2(10), 5 cycle date 6 ) 7 partition by list(region) 8 subpartition by range(cycle) 9 interval 10 (numtoyminterval(1,'month')) 11 subpartition template 12 (subpartition sp1 values less than (to_date('2016-1-1','yyyy-mm-dd'))) 13 ( 14 partition p1 values('010'), 15 partition p2 values('020'), 16 partition p3 values('0311') 17 );Table created.SQL> @tabpart anbob_t5TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE_RAW COMPRESS INDE INMEMORY---------- ----------- ---- --- -------------- ------------------ -------------- -------- ---- --------ANBOB ANBOB_T5 1 YES P1 1048575 '010' NONE NONE NONEANBOB ANBOB_T5 2 YES P2 1048575 '020' NONE NONE NONEANBOB ANBOB_T5 3 YES P3 1048575 '0311' NONE NONE NONESQL> @tabsubpartTABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS HIGH_VALUE_RAW ---------- ------------ -------------- ------------------ ---------- ------------------------------- ANBOB ANBOB_T5 P1 P1_SP1 1 TO_DATE(' 2016-01-01 00:00:00', ANBOB ANBOB_T5 P2 P2_SP1 1 TO_DATE(' 2016-01-01 00:00:00', ANBOB ANBOB_T5 P3 P3_SP1 1 TO_DATE(' 2016-01-01 00:00:00', SQL> insert into anbob_t5 select rownum,'anbob.com','0311',add_months(sysdate,rownum) from dual connect by rownum<=12;12 rows created. SQL> @tabsubpartTABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS HIGH_VALUE_RAW ---------- ------------- --------------- ------------------ ------- -------------------------------ANBOB ANBOB_T5 P1 P1_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P2 P2_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P3 P3_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1578 2 TO_DATE(' 2017-03-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1579 3 TO_DATE(' 2017-04-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1580 4 TO_DATE(' 2017-05-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1581 5 TO_DATE(' 2017-06-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1582 6 TO_DATE(' 2017-07-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1583 7 TO_DATE(' 2017-08-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1584 8 TO_DATE(' 2017-09-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1585 9 TO_DATE(' 2017-10-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1586 10 TO_DATE(' 2017-11-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1587 11 TO_DATE(' 2017-12-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1588 12 TO_DATE(' 2018-01-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1589 13 TO_DATE(' 2018-02-01 00:00:00',15 rows selected.

Note:

上面创建了一个interval subpartition的表, 以月分步长,后来insert一部分数据,interval subpartition自动生成了其子分区.

5, 自动list + interval subpartition

如果把auto list维护和interval subpartition组合, 这样几乎实现了对数据扩展的完全自动化, 不过在我当前的bate版本发现insert connect的形式有些问题,还不确认是否是当前版本的bug, 同样我的weibo之前有发过测试12.2 SQL功能推荐的https://livesql.oracle.com 上发现提示该特性不支持. 但不影响测试, 相信会在后面的版本中修复. 继续使用上面创建的表.

SQL> alter table anbob_t5 set partitioning automatic; Table altered.SQL> insert into anbob_t5 2 select rownum,'anbob.com','021',add_months(sysdate,rownum) from dual connect by rownum<=4; insert into anbob_t5 * ERROR at line 1: ORA-14401: inserted partition key is outside specified partition SQL> select PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,AUTOLIST,DEF_READ_ONLY from user_part_tables where table_name='ANBOB_T5';PARTITION PARTITIONING_KEY_COUNT AUT DEF--------- ---------------------- --- ---LIST 1 YES NOSQL> @tabpart anbob_t5TABLE_OWNE TABLE_NAME POS COM PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE_RAW COMPRESS INDE INMEMORY---------- ----------- ---- --- --------------- ------------------ -------------- -------- ---- --------ANBOB ANBOB_T5 1 YES P1 1048575 '010' NONE NONE NONEANBOB ANBOB_T5 2 YES P2 1048575 '020' NONE NONE NONEANBOB ANBOB_T5 3 YES P3 1048575 '0311' NONE NONE NONEANBOB ANBOB_T5 4 YES SYS_P1591 1048575 '021' NONE NONE NONESQL> @tabsubpartTABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS HIGH_VALUE_RAW ---------- ----------- -------------- ----------------- ------- ------------------------------- ANBOB ANBOB_T5 P1 P1_SP1 1 TO_DATE(' 2016-01-01 00:00:00', ANBOB ANBOB_T5 P2 P2_SP1 1 TO_DATE(' 2016-01-01 00:00:00', ANBOB ANBOB_T5 P3 P3_SP1 1 TO_DATE(' 2016-01-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1578 2 TO_DATE(' 2017-03-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1579 3 TO_DATE(' 2017-04-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1580 4 TO_DATE(' 2017-05-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1581 5 TO_DATE(' 2017-06-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1582 6 TO_DATE(' 2017-07-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1583 7 TO_DATE(' 2017-08-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1584 8 TO_DATE(' 2017-09-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1585 9 TO_DATE(' 2017-10-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1586 10 TO_DATE(' 2017-11-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1587 11 TO_DATE(' 2017-12-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1588 12 TO_DATE(' 2018-01-01 00:00:00', ANBOB ANBOB_T5 P3 SYS_SUBP1589 13 TO_DATE(' 2018-02-01 00:00:00', ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1590 1 TO_DATE(' 2016-01-01 00:00:00', 16 rows selected.SQL> insert into anbob_t5 2 select rownum,'anbob.com','021',add_months(sysdate,rownum) from dual;1 row created.SQL> select * from anbob_t5 partition(SYS_P1591); ID NAME REGION CYCLE---------- -------------------- ---------- ------------------- 1 anbob.com 021 2017-02-04 11:38:28SQL> insert into anbob_t5 2 with c as ( 3 select rownum,'anbob.com','021',add_months(sysdate,rownum) from dual connect by rownum<=4 4 ) 5 select * from c; 4 rows created. SQL> select * from anbob_t5 partition(SYS_P1591); ID NAME REGION CYCLE---------- -------------------- ---------- ------------------- 1 anbob.com 021 2017-02-04 11:38:28 1 anbob.com 021 2017-02-04 11:39:39 2 anbob.com 021 2017-03-04 11:39:39 3 anbob.com 021 2017-04-04 11:39:39 4 anbob.com 021 2017-05-04 11:39:39SQL> @tabsubpart;TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME SUB_POS HIGH_VALUE_RAW ---------- ----------- ---------------- ------------------ --------- -------------------------------ANBOB ANBOB_T5 P1 P1_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P2 P2_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P3 P3_SP1 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1578 2 TO_DATE(' 2017-03-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1579 3 TO_DATE(' 2017-04-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1580 4 TO_DATE(' 2017-05-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1581 5 TO_DATE(' 2017-06-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1582 6 TO_DATE(' 2017-07-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1583 7 TO_DATE(' 2017-08-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1584 8 TO_DATE(' 2017-09-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1585 9 TO_DATE(' 2017-10-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1586 10 TO_DATE(' 2017-11-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1587 11 TO_DATE(' 2017-12-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1588 12 TO_DATE(' 2018-01-01 00:00:00',ANBOB ANBOB_T5 P3 SYS_SUBP1589 13 TO_DATE(' 2018-02-01 00:00:00',ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1590 1 TO_DATE(' 2016-01-01 00:00:00',ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1592 2 TO_DATE(' 2017-03-01 00:00:00',ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1593 3 TO_DATE(' 2017-04-01 00:00:00',ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1594 4 TO_DATE(' 2017-05-01 00:00:00',ANBOB ANBOB_T5 SYS_P1591 SYS_SUBP1595 5 TO_DATE(' 2017-06-01 00:00:00',20 rows selected.SQL>

NOTE:

如果使用了auto list+ interval subpartition的组合, 使用connect by 这种递规的查询的insert方式会失败, 但是分区会自动创建数据rollback.如果insert 一条是可以成功. 同样如果改成with connect的方式也是可以成功的.

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台