继续我的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 近乎全自动
以下所有测试使用的版本是:
-- demo --
1, 多列(multi-column) list partitioning
在12c r2之前list分区只支持1列, 无法满足一些数据存储模型的拆分, 这个新特性引入list也可以像range一样在一个维度指定多列的分区表或子分区表. 目前最多支持16个keys列, 同时也支持12c新特性的外部分区表和Reference partition及12c新引入auto-list特性的分区表.
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分区一样是系统生成的, 该特性可以打开和关闭, 默认关闭.
接着上面创建的表
3, 分区级的read only
在之前的版本中配置table的只读属性只能是表级, 12.2可以在分区或子分区级修改只读属性.
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).
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 上发现提示该特性不支持. 但不影响测试, 相信会在后面的版本中修复. 继续使用上面创建的表.
NOTE:
如果使用了auto list+ interval subpartition的组合, 使用connect by 这种递规的查询的insert方式会失败, 但是分区会自动创建数据rollback.如果insert 一条是可以成功. 同样如果改成with connect的方式也是可以成功的.
主要新特性有:
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的方式也是可以成功的.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
