12c新特性：多列list分区

从12.2开始，oracle支持建立多个列组成分区键的分区表了。创建多列list分区表与一般的list分区并无太大的区别，除了在指定分区键时包含了用逗号隔开的多个列。每个独立的分区可以包含多列的集合，通常每张表只会有一个DEFAULT分区，不支持子DEFAULT分区。

多列list分区

新建一个多列list分区，插入部分数据

drop table t1 purge;

create table t1 (
id number,
prov varchar2(10),
name varchar2(10)
)
partition by list (prov,name)
(
    partition p1 values(('HU','hubei'),('BJ','beijing')),
    partition p2 values(('SH','shanghai')),
    partition pdefault values (DEFAULT)
);

insert into t1 values(1,'HU','hubei');
insert into t1 values(2,'BJ','beijing');
insert into t1 values(3,'SH','shanghai');
insert into t1 values(4,'ZJ','zhejiang');
commit;

exec dbms_stats.gather_table_stats(user,'T1');

查看分区表数据的分区情况

column table_name format a20
column partition_name format a20
column high_value format a40

select table_name,
       partition_name,
       high_value
from   user_tab_partitions
where  table_name = 'T1'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE
-------------------- -------------------- ----------------------------------------
T1                   P1                   ( 'HU', 'hubei' ), ( 'BJ', 'beijing' )
T1                   P2                   ( 'SH', 'shanghai' )
T1                   PDEFAULT             DEFAULT

这里可以看到符合分区要求的数据都落在了对应的分区内，而zhejiang则落在了DEFAULT分区内。

可以通过分区扩展语法来查询每个分区的数据

xb@PDB12C> select * from t1 partition(p1);

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei
         2 BJ         beijing

也可以通过直接带入分区包含的数据的方式

xb@PDB12C> select * from t1 partition for('HU','hubei');

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei
         2 BJ         beijing

这里也可以直接将P1分区拆分成多个分区

xb@PDB12C> alter table t1 split partition p1 into (partition p1a values('HU','hubei'),partition p1b) online;

Table altered.

xb@PDB12C> select * from t1 partition(p1a);

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei

xb@PDB12C> select * from t1 partition(p1b);

        ID PROV       NAME
---------- ---------- ----------
         2 BJ         beijing

在线对分区表的操作并不会影响DML操作，这个也是12c引入的新特性。

这里有一些多列lsit分区的特性要说明一下：

• 可以指定超过一个列来作为分区键

  最高不超过16个列

  每个分区键必须唯一

• 不支持对单列级别再指定默认分区，比如(‘HU’,DEFALUT)的情况是不允许的，避免造成歧义

• 支持函数

  支持分区级别和子分区级别

  支持堆表

  支持外部表

  支持引用分区和自动列分区

多列的自动list分区

auto-list partition：12c新特性：自动创建list分区

多列跟之前的一样也支持自动list，不过这时就要把default分区去掉。

drop table t1 purge;

create table t1 (
id number,
prov varchar2(10),
name varchar2(10)
)
partition by list (prov,name) AUTOMATIC                 >>>>====
(
    partition p1 values(('HU','hubei'),('BJ','beijing')),
    partition p2 values(('SH','shanghai'))
);

insert into t1 values(1,'HU','hubei');
insert into t1 values(2,'BJ','beijing');
insert into t1 values(3,'SH','shanghai');
insert into t1 values(4,'ZJ','zhejiang');
commit;

exec dbms_stats.gather_table_stats(user,'T1');

查看不在分区条件范围内的zhejiang 数据

column table_name format a20
column partition_name format a20
column high_value format a40

select table_name,
       partition_name,
       high_value
from   user_tab_partitions
where  table_name = 'T1'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE
-------------------- -------------------- ----------------------------------------
T1                   P1                   ( 'HU', 'hubei' ), ( 'BJ', 'beijing' )
T1                   P2                   ( 'SH', 'shanghai' )
T1                   SYS_P1197            ( 'ZJ', 'zhejiang' )