12.2 new feature of partition

Oracle database 12.2有不少分区加强的特性：

Multi-Column ListPartitioning

Auto list Partitioning

Interval SubPartitioning

Online Partition Maintenance Operation

Online Table Conversion to Partition Table

Filtered Partitioning Maintenance Operation

Read Only Partitions

我们来列举几个看看：

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_ ，SYS_RMTAB$$_H ，SYS_RMTAB$$_I以及上面的索引，在进行捣鼓，另外还有一堆数据字典的更新。没有看到类似dbms_redefinition在线重定义的功能的介入，没有看到在线重定义时关于物化视图create snaphot，和MLOG$_XXX这样的关键字。

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>

12.2 new feature of partition

相关文章

18c新特性的一些小结

关于oradebug -prelim

见到Oracle 12c的踪影了

发表回复 取消回复

发表回复取消回复