Oracle 12c Study之-- 分区表新特性
数据库版本：
12:05:07 SYS@prod>select * from v$version;
BANNER CON_ID
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Elapsed: 00:00:00.03
一、 参考性分区新特性
在参考性分区增加间隔分区的功能，当父表增加新的分区时，子表扩展分区时，也会扩展同样的分区。
案例：
11:56:33 SCOTT@ prod>create table new_orders
12:03:04 2 partition by range (order_date)
12:03:04 3 INTERVAL(NUMTOYMINTERVAL(1, 'month')) store in(tbs1,tbs2,tbs3,tbs4)
12:03:04 4 (partition p1 values less than (to_date('2005-04-01','yyyy-mm-dd'))tablespace tbs1,
12:03:04 5 partition p2 values less than (to_date('2005-07-01','yyyy-mm-dd'))tablespace tbs2,
12:03:04 6 partition p3 values less than (to_date('2005-10-01','yyyy-mm-dd'))tablespace tbs3,
12:03:04 7 partition p4 values less than (to_date('2006-01-01','yyyy-mm-dd'))tablespace tbs4)
12:03:04 8 as
12:03:04 9 select * from orders;
Table created.
插入数据：
insert into new_ordersvalues(101,to_date('2005-02-03','yyyy-mm-dd'),'x1',1001,1,20000,88001,88002);
insert into new_ordersvalues(102,to_date('2005-05-15','yyyy-mm-dd'),'x1',1002,1,30000,88002,88002);
insert into new_orders values(103,to_date('2005-09-22','yyyy-mm-dd'),'x1',1003,1,40000,88003,88002);
insert into new_ordersvalues(104,to_date('2005-12-30','yyyy-mm-dd'),'x1',1004,1,10000,88004,88002);
12:06:24 SCOTT@ prod>col partition_name fora20
12:06:37 SCOTT@ prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
wheresegment_name='NEW_ORDERS'
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------ --------------------
NEW_ORDERS TABLE PARTITION P1 TBS1
NEW_ORDERS TABLE PARTITION P2 TBS2
NEW_ORDERS TABLE PARTITION P3 TBS3
NEW_ORDERS TABLE PARTITION P4 TBS4
在父表上建立主键:
12:06:37 SCOTT@ prod>alter table new_orders add constraint pk_new_orders primarykey(order_id);
Table altered.
建立参考分区：
12:08:06 SCOTT@ prod>CREATE TABLE order_items
12:09:35 2 ( order_id NUMBER(12)NOT NULL,
12:09:35 3 line_item_id NUMBER(3) NOT NULL,
12:09:35 4 product_id NUMBER(6) NOT NULL,
12:09:35 5 unit_price NUMBER(8,2),
12:09:35 6 quantity NUMBER(8),
12:09:35 7 CONSTRAINT order_items_fk
12:09:35 8 FOREIGN KEY(order_id) REFERENCESnew_orders(order_id)
12:09:35 9 )
12:09:35 10 PARTITION BY REFERENCE(order_items_fk);
Table created.
插入测试数据：
insert into order_itemsvalues(101,801,200001,200,100);
insert into order_itemsvalues(102,802,200002,300,100);
insert into order_itemsvalues(103,803,200003,400,100);
insert into order_itemsvalues(104,804,200004,100,100);
12:10:39 SCOTT@ prod>select* from order_items;
ORDER_IDLINE_ITEM_ID PRODUCT_ID UNIT_PRICE QUANTITY
---------- ------------ ---------- --------------------
101 801 200001 200 100
102 802 200002 300 100
103 803 200003 400 100
104 804 200004 100 100
Elapsed: 00:00:00.34
12:12:46 SCOTT@ prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:12:53 2 where segment_name in ('NEW_ORDERS','ORDER_ITEMS');
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
ORDER_ITEMS TABLE PARTITION P1 TBS1
NEW_ORDERS TABLE PARTITION P1 TBS1
ORDER_ITEMS TABLE PARTITION P3 TBS3
ORDER_ITEMS TABLE PARTITION P2 TBS2
NEW_ORDERS TABLE PARTITION P3 TBS3
NEW_ORDERS TABLE PARTITION P2 TBS2
ORDER_ITEMS TABLE PARTITION P4 TBS4
NEW_ORDERS TABLE PARTITION P4 TBS4
父表中插入数据，超越分区范围：
12:13:00 SCOTT@ prod>insert into new_ordersvalues(105,to_date('2006-02-01','yyyy-mm-dd'),'x1',1005,1,10000,88005,88002);
1 row created.
12:16:31 SCOTT@ prod>commit;
Commit complete.
12:16:34 SCOTT@ prod>select * from new_orders;
ORDER_IDORDER_DAT PROD_NAME LINE_ITEM_ID CHANNE_ID PRODUCT_ID UNIT_PRICE QTY
---------- --------- ---------- ---------------------- ---------- ----------
10103-FEB-05 x1 1001 1 20000 88001 88002
10215-MAY-05 x1 1002 1 30000 88002 88002
10322-SEP-05 x1 1003 1 40000 88003 88002
10430-DEC-05 x1 1004 1 10000 88004 88002
10501-FEB-06 x1 1005 1 10000 88005 88002
父表自动扩展一个新的分区：
12:17:17 SCOTT@ prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:17:34 2 where segment_name in ('NEW_ORDERS','ORDER_ITEMS') order by 1;
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
NEW_ORDERS TABLE PARTITION SYS_P346 TBS2
NEW_ORDERS TABLE PARTITION P2 TBS2
NEW_ORDERS TABLE PARTITION P3 TBS3
NEW_ORDERS TABLE PARTITION P4 TBS4
NEW_ORDERS TABLE PARTITION P1 TBS1
ORDER_ITEMS TABLE PARTITION P2 TBS2
ORDER_ITEMS TABLE PARTITION P3 TBS3
ORDER_ITEMS TABLE PARTITION P4 TBS4
ORDER_ITEMS TABLE PARTITION P1 TBS1
9 rows selected.
在子表中插入超出分区范围的数据：
12:17:49 SCOTT@ prod>insert into order_items values(105,805,200005,100,100);
1 row created.
12:19:55 SCOTT@ prod>commit;
Commit complete.
子表也自动扩展一个新的分区，且分区名和父表分区名相同：
12:19:57 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:20:05 2 where segment_name in ('NEW_ORDERS','ORDER_ITEMS') order by 1;
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
NEW_ORDERS TABLE PARTITION P1 TBS1
NEW_ORDERS TABLE PARTITION P2 TBS2
NEW_ORDERS TABLE PARTITION P3 TBS3
NEW_ORDERS TABLE PARTITION SYS_P346 TBS2
NEW_ORDERS TABLE PARTITION P4 TBS4
ORDER_ITEMS TABLE PARTITION P4 TBS4
ORDER_ITEMS TABLE PARTITION P3 TBS3
ORDER_ITEMS TABLE PARTITION SYS_P346 TBS2
ORDER_ITEMS TABLE PARTITION P1 TBS1
ORDER_ITEMS TABLE PARTITION P2 TBS2
10 rows selected.
二、多分区操作和管理
1） 增加多个新分区
在Oracle 12c R1之前，一次只可能添加一个新分区到一个已存在的分区表。要添加一个以上的新分区，需要对每个新分区都单独执行一次ALTER TABLE ADD PARTITION语句。而Oracle 12c只需要使用一条单独的ALTER TABLE ADD PARTITION 命令就可以添加多个新分区，这增加了数据库灵活性。以下示例说明了如何添加多个新分区到已存在的分区表：
12:22:48 SCOTT@ prod>CREATE TABLE t1_part (enonumber(8), ename varchar2(40), sal number (6))
12:44:27 2 PARTITION BY RANGE (sal)
12:44:27 3 (PARTITION p1 VALUES LESS THAN (10000),
12:44:27 4 PARTITION p2 VALUES LESS THAN (20000),
12:44:27 5 PARTITION p3 VALUES LESS THAN (30000) );
Table created.
12:44:28 SCOTT@ prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:44:35 2 where segment_name='T1_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T1_PART TABLE PARTITION P1 USERS
T1_PART TABLE PARTITION P2 USERS
T1_PART TABLE PARTITION P3 USERS
增加多个新分区到表中：
12:50:45 SCOTT@prod>ALTER TABLE t1_part ADD PARTITION p4 VALUES LESS THAN (35000),PARTITION p5 VALUES LESS THAN (40000);
Table altered.
12:51:07 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME from user_segments
12:51:50 2 where segment_name='T1_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T1_PART TABLE PARTITION P1 USERS
T1_PART TABLE PARTITION P2 USERS
T1_PART TABLE PARTITION P3 USERS
T1_PART TABLE PARTITION P4 USERS
T1_PART TABLE PARTITION P5 USERS
同样，只要MAXVALUE分区不存在，你就可以添加多个新分区到一个列表和系统分区表。
2） 同时截断多个分区
作为数据维护的一部分，DBA通常会在一个分区表上进行删除或截断分区的维护任务。在12c R1之前，对于一个已存在的分区表一次只可能删除或截断一个分区。而对于Oracle12c， 可以用单条ALTER TABLE table_name {DROP|TRUNCATE}PARTITIONS 命令来撤销或合并多个分区和子分区。
12:53:00 SCOTT@ prod>ALTERTABLE t1_part TRUNCATE partitions p4,p5;
Table truncated.
12:53:39 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:53:45 2 where segment_name='T1_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T1_PART TABLE PARTITION P1 USERS
T1_PART TABLE PARTITION P2 USERS
T1_PART TABLE PARTITION P3 USERS
T1_PART TABLE PARTITION P4 USERS
T1_PART TABLE PARTITION P5 USERS
Elapsed: 00:00:00.02
3） 同时删除多个分区
4）
12:53:48 SCOTT@prod>alter table t1_part drop partitions p4,p5;
Table altered.
12:54:19 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAMEfrom user_segments
12:54:24 2 where segment_name='T1_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T1_PART TABLE PARTITION P1 USERS
T1_PART TABLE PARTITION P2 USERS
T1_PART TABLE PARTITION P3 USERS
Elapsed: 00:00:00.02
截断、删除分区时同时更新全局索引
要保持索引更新，使用UPDATE INDEXES或UPDATE GLOBAL INDEXES语句，如下所示：
12:56:09 SCOTT@prod>ALTER TABLE t1_part TRUNCATE partitions p2,p3 update global indexes;
Table truncated.
12:57:09 SCOTT@prod>ALTER TABLE t1_part dop partitionsp2,p3 update global indexes;
如果你在不使用UPDATE GLOBAL INDEXES 语句的情况下删除或截断一个分区，你可以在USER_INDEXES或USER_IND_PARTITIONS 字典视图下查询ORPHANED_ENTRIES字段以找出是否有索引包含任何的过期条目。
5） 同时拆分多个分区：
在12c中新增强的SPLIT PARTITION 语句可以让你只使用一个单独命令将一个特定分区或子分区分割为多个新分区。
12:57:59 SCOTT@prod>CREATE TABLE t2_part (eno number(8), ename varchar2(40), sal number(6))
12:58:11 2 PARTITION BY RANGE (sal)
12:58:14 3 (PARTITION p1 VALUES LESS THAN (10000),
12:58:18 4 PARTITION p2 VALUES LESS THAN (20000),
12:58:21 5 PARTITION p_max VALUES LESS THAN (MAXVALUE) );
Table created.
12:58:30 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
12:58:36 2 where segment_name='T2_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T2_PART TABLE PARTITION P1 USERS
T2_PART TABLE PARTITION P2 USERS
T2_PART TABLE PARTITION P_MAX USERS
Elapsed: 00:00:00.03
12:58:56 SCOTT@prod>ALTER TABLE t2_part
13:00:23 2 SPLIT PARTITION p_max INTO
13:00:23 3 (PARTITION p3 VALUES LESS THAN (25000),
13:00:23 4 PARTITION p4 VALUES LESS THAN (30000),
13:00:23 5 PARTITION p_max);
Table altered.
13:00:24 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
13:00:36 2 where segment_name='T2_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T2_PART TABLE PARTITION P1 USERS
T2_PART TABLE PARTITION P2 USERS
T2_PART TABLE PARTITION P3 USERS
T2_PART TABLE PARTITION P4 USERS
T2_PART TABLE PARTITION P_MAX USERS
Elapsed: 00:00:00.04
6） 同时合并多个分区
你可以使用单条ALTER TBALE MERGE PARTITIONS 语句将多个分区合并为一个单独分区：
13:00:39 SCOTT@prod> CREATE TABLE t3_part (eno number(8), ename varchar2(40), sal number(6))
13:02:12 2 PARTITION BY RANGE (sal)
13:02:12 3 (PARTITION p1 VALUES LESS THAN (10000),
13:02:12 4 PARTITION p2 VALUES LESS THAN (20000),
13:02:12 5 PARTITION p3 VALUES LESS THAN (30000),
13:02:12 6 PARTITION p4 VALUES LESS THAN (40000),
13:02:12 7 PARTITION p5 VALUES LESS THAN (50000),
13:02:12 8 PARTITION p_max VALUES LESSTHAN (MAXVALUE) );
Table created.
13:02:14 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
13:02:21 2 where segment_name='T3_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T3_PART TABLE PARTITION P_MAX USERS
T3_PART TABLE PARTITION P5 USERS
T3_PART TABLE PARTITION P4 USERS
T3_PART TABLE PARTITION P3 USERS
T3_PART TABLE PARTITION P2 USERS
T3_PART TABLE PARTITION P1 USERS
如果分区范围形成序列，你可以使用如下示例：
13:06:12 SCOTT@prod>alter table t3_part merge partitions p2 to p3 into partition p_m23;
Table altered.
13:06:38 SCOTT@prod>select segment_name,segment_type,PARTITION_NAME,TABLESPACE_NAME fromuser_segments
13:06:44 2 where segment_name='T3_PART';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
-------------------- --------------------------------------
T3_PART TABLE PARTITION P1 USERS
T3_PART TABLE PARTITION P_M23 USERS
T3_PART TABLE PARTITION P_MAX USERS
T3_PART TABLE PARTITION P_MERG USERS