Oracle database 12.2有不少分区加强的特性：
我们来列举几个看看：
1. multi-column list partition。注：最多支持16个列
Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.0.1
Connected as test@ORA122_windows_pdb122
SQL>
CREATE TABLE t_oracleblog (salername varchar(200),region VARCHAR2(50), channel VARCHAR2(50))
PARTITION BY LIST (region, channel) --Note keywork :region, channel, Here are 2 columns
(
partition p1 values ('USA','Direct'),
partition p2 values ('USA','Partners'),
partition p3 values ('GERMANY','Direct'),
partition p4 values (('GERMANY','Partners'),('GERMANY','Web')),
partition p5 values ('CHINA','Direct'),
partition p6 values (('CHINA','Partners'),('CHINA','Web'),('CHINA','Oversee')),
partition p7 values ('JAPAN','Direct'),
partition p8 values (DEFAULT)
)
/
insert into t_oracleblog values('AAA','USA','Direct');
insert into t_oracleblog values('BBB','CHINA','Direct');
insert into t_oracleblog values('CCC','CHINA','Web');
insert into t_oracleblog values('DDD','CHINA','Partners');
insert into t_oracleblog values('EEE','GERMANY','Direct');
insert into t_oracleblog values('FFF','GERMANY','Partners');
insert into t_oracleblog values('GGG','JAPAN','Direct');
insert into t_oracleblog values('HHH','CHINA','Oversee');
insert into t_oracleblog values('III','JAPAN','Web');
insert into t_oracleblog values('JJJ','FRANCE','Direct');
insert into t_oracleblog values('KKK','CHINA','DIRECT');
SQL> select * from t_oracleblog partition(p1);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
AAA USA Direct
SQL> select * from t_oracleblog partition(p2);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
SQL> select * from t_oracleblog partition(p3);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
EEE GERMANY Direct
SQL> select * from t_oracleblog partition(p4);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
FFF GERMANY Partners
SQL> select * from t_oracleblog partition(p5);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
BBB CHINA Direct
SQL> select * from t_oracleblog partition(p6);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
CCC CHINA Web
DDD CHINA Partners
HHH CHINA Oversee
SQL> select * from t_oracleblog partition(p7);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
GGG JAPAN Direct
SQL> select * from t_oracleblog partition(p8);
SALERNAME REGION CHANNEL
-------------------- -------------------------------------------------- --------------------------------------------------
III JAPAN Web
JJJ FRANCE Direct
KKK CHINA DIRECT
SQL>
2. auto-list partition
CREATE TABLE t_car (brand VARCHAR2(50),model VARCHAR2(50), year char(4))
PARTITION BY LIST (brand) AUTOMATIC --Note keywork :AUTOMATIC
(
partition p1 values ('BMW'),
partition p2 values ('BENZ')
)
/
SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
T_CAR P1
T_CAR P2
SQL>
SQL>
SQL>
SQL>
SQL> insert into t_car values('BMW','AAA','1984');
1 row inserted
SQL> insert into t_car values('BMW','BBB','1986');
1 row inserted
SQL> insert into t_car values('BENZ','CCC','1992');
1 row inserted
SQL> insert into t_car values('BENZ','DDD','1983');
1 row inserted
SQL>
SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
T_CAR P1
T_CAR P2
SQL>
SQL>
SQL>
SQL> insert into t_car values('JEEP','EEE','1991'); ---插入之前没有在partition key定义的行。
1 row inserted
SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
T_CAR P1
T_CAR P2
T_CAR SYS_P1328
SQL>
SQL>
SQL>
SQL> insert into t_car values('BYD','FFF','2015');
1 row inserted
SQL> insert into t_car values('FORD','FFF','2015');
1 row inserted
SQL>
SQL>
SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR'; --可以看到自动生成了新分区。
TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
T_CAR P1
T_CAR P2
T_CAR SYS_P1328
T_CAR SYS_P1329
T_CAR SYS_P1330
SQL>
3. interval subpartition
4.online DDL for partition
CREATE TABLE t_oracleblog (salername varchar(200),region VARCHAR2(50), channel VARCHAR2(50));
ALTER TABLE t_oracleblog MODIFY
PARTITION BY LIST (region)
(partition p1 values ('USA'),
partition p2 values ('GERMANY'),
partition p3 values ('JAPAN'),
partition p4 values (DEFAULT))
ONLINE ---Note keyword: ONLINE
/
注1：统计信息会收集
注2：从10046的trace看，似乎是临时创建了SYS_JOURNAL_
5. Filtered Partition on Maintenance Operations
在MOVE，SPLIT，MERGE partition的时候，可以进行过滤：
SQL> select * from T_ORACLEBLOG partition(p4); SALERNAME REGION CHANNEL -------------------- -------------------------------------------------- -------------------------------------------------- BBB CHINA Direct CCC CHINA Web DDD CHINA Partners HHH CHINA Oversee JJJ FRANCE Direct KKK CHINA DIRECT 6 rows selected SQL> SQL> SQL> SQL> SQL> SQL> SQL> ALTER TABLE T_ORACLEBLOG MOVE PARTITION p4 2 TABLESPACE SYSAUX 3 INCLUDING ROWS WHERE REGION = 'CHINA' --Note keyword INCLUDING ROW WHERE 4 / Table altered SQL> SQL> select * from T_ORACLEBLOG partition(p4); SALERNAME REGION CHANNEL -------------------- -------------------------------------------------- -------------------------------------------------- BBB CHINA Direct CCC CHINA Web DDD CHINA Partners HHH CHINA Oversee KKK CHINA DIRECT SQL>
注1：where条件后面的字段千万不能写错，不然数据全没了。如错写成INCLUDING ROWS WHERE channel = ‘CHINA’，MOVE之后则分区4的数据全没了。因为including row表示留下的数据，而channel = ‘CHINA’ 这样的数据一条都没有，所以就清空了分区。
6.Read only partition
SQL> CREATE TABLE orders
2 (
3 order_id number,
4 order_date DATE,
5 customer_name varchar2(200)
6 ) read only ----Note keyword read only, which mean table read only
7 PARTITION BY RANGE(order_date)
8 (
9 partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')),
10 partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
11 partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')),
12 partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) read write ----Note keyword read only, which mean partition q4 read write
13 )
14 /
Table created
SQL>
SQL>
SQL> insert into orders values(1,to_date('2015-04-20','yyyy-mm-dd'),'AAA');
1 row inserted
SQL> insert into orders values(1,to_date('2015-06-20','yyyy-mm-dd'),'AAA');
1 row inserted
SQL> insert into orders values(1,to_date('2015-01-20','yyyy-mm-dd'),'AAA'); --对于read only的partition，插入数据会报错。
insert into orders values(1,to_date('2015-01-20','yyyy-mm-dd'),'AAA')
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SQL>
SQL>
SQL> select * from orders;
ORDER_ID ORDER_DATE CUSTOMER_NAME
---------- ----------- --------------------------------------------------------------------------------
1 2015/4/20 AAA
1 2015/6/20 AAA
SQL>