首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 12c New Feature: Partition增强(四) multi-column list, auto-list ,interval subpartition, partition level readonly
Oracle 12c New Feature: Partition增强(四) multi-column list, auto-list ,interval subpartition, partition level readonly
继续我的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 0 PL/SQL Release 12.2.0.0.1 - Beta 0 CORE 12.2.0.0.0 Beta 0 TNS for Linux: Version 12.2.0.0.0 - Beta 0 NLSRTL 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 r2
SQL> 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 r2
SQL> 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_t4
TABLE_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 DISABLED
ANBOB ANBOB_T4 2 NO P2 0 ( '020', '2016' ) DISABLED ON DISABLED
ANBOB ANBOB_T4 3 NO P3 0 ( '0311', '2016' ) DISABLED ON DISABLED
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 NO NO
SQL> @partkeys anbob_t4
PARTK OWNER NAME COLUMN_NAME COLUMN_POSITION
----- ------- - --------- - ------------ - ---------------
TABLE ANBOB ANBOB_T4 REGION 1
TABLE 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 off
SQL> 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 NO
SQL> @tabpart ANBOB_T4
TABLE_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 DISABLED
ANBOB ANBOB_T4 2 NO P2 0 ( '020', '2016' ) DISABLED ON DISABLED
ANBOB ANBOB_T4 3 NO P3 0 ( '0311', '2016' ) DISABLED ON DISABLED
ANBOB 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 YES 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' ) YES ANBOB_T4 P2 ( '020', '2016' ) YES ANBOB_T4 P3 ( '0311', '2016' ) YES ANBOB_T4 SYS_P1558 ( '0311', '2017' ) YES SQL> 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' ) YES ANBOB_T4 P2 ( '020', '2016' ) YES ANBOB_T4 P3 ( '0311', '2016' ) NO ANBOB_T4 SYS_P1558 ( '0311', '2017' ) YES SQL> 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 level SQL> 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_t5 TABLE_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 NONE ANBOB ANBOB_T5 2 YES P2 1048575 '020' NONE NONE NONE ANBOB ANBOB_T5 3 YES P3 1048575 '0311' NONE NONE NONE SQL> @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', SQL> insert into anbob_t5 select rownum,'anbob.com','0311',add_months(sysdate,rownum) from dual connect by rownum<=12; 12 rows created. SQL> @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', 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 NO SQL> @tabpart anbob_t5 TABLE_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 NONE ANBOB ANBOB_T5 2 YES P2 1048575 '020' NONE NONE NONE ANBOB ANBOB_T5 3 YES P3 1048575 '0311' NONE NONE NONE ANBOB ANBOB_T5 4 YES SYS_P1591 1048575 '021' NONE NONE NONE SQL> @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', 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:28 SQL> 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:39 SQL> @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的方式也是可以成功的.
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。