首页 » ORACLE, ORACLE [C]系列 » 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)评论关闭。

  1. lara | #1
    2017-01-17 at 17:26

    Nice presentation about the points in the blog and detailed explanation about the oracle is very good.