Oracle 12c Study之-- 分区表新特性

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

CORE12.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 2partition by range (order_date)

12:03:04 3INTERVAL(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 5partition p2 values less than (to_date('2005-07-01','yyyy-mm-dd'))tablespace tbs2,

12:03:04 6partition p3 values less than (to_date('2005-10-01','yyyy-mm-dd'))tablespace tbs3,

12:03:04 7partition p4 values less than (to_date('2006-01-01','yyyy-mm-dd'))tablespace tbs4)

12:03:04 8as

12:03:04 9select * 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',1002,30000,88002,88002);

insert into new_orders values(103,to_date('2005-09-22',1003,40000,88003,88002);

insert into new_ordersvalues(104,to_date('2005-12-30',1004,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_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- ------------------ --------------------

NEW_ORDERSTABLE PARTITION P1 TBS1

NEW_ORDERSTABLE PARTITION P2 TBS2

NEW_ORDERSTABLE PARTITION P3 TBS3

NEW_ORDERSTABLE 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 3line_item_id NUMBER(3) NOT NULL,

12:09:35 4product_id NUMBER(6) NOT NULL,

12:09:35 5unit_price NUMBER(8,2),

12:09:35 6quantity NUMBER(8),

12:09:35 7CONSTRAINT order_items_fk

12:09:35 8FOREIGN 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_PRICEQUANTITY

---------- ------------ ---------- --------------------

101 801 200001 200 100

102 802 200002300 100

103 803 200003 400 100

104 804 200004 100 100

Elapsed: 00:00:00.34

12:12:46 SCOTT@ prod>select segment_name,TABLESPACE_NAME fromuser_segments

12:12:532 where segment_name in ('NEW_ORDERS','ORDER_ITEMS');

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

ORDER_ITEMSTABLE PARTITION P1 TBS1

NEW_ORDERSTABLE PARTITION P1 TBS1

ORDER_ITEMSTABLE PARTITION P3 TBS3

ORDER_ITEMSTABLE PARTITION P2 TBS2

NEW_ORDERSTABLE PARTITION P3 TBS3

NEW_ORDERSTABLE PARTITION P2 TBS2

ORDER_ITEMSTABLE PARTITION P4 TBS4

NEW_ORDERSTABLE PARTITION P4 TBS4

父表中插入数据，超越分区范围：

12:13:00 SCOTT@ prod>insert into new_ordersvalues(105,to_date('2006-02-01',1005,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 120000 88001 88002

10215-MAY-05 x1 1002 130000 88002 88002

10322-SEP-05 x1 1003 140000 88003 88002

10430-DEC-05 x1 1004 110000 88004 88002

10501-FEB-06 x1 1005 110000 88005 88002

父表自动扩展一个新的分区：

12:17:17 SCOTT@ prod>select segment_name,TABLESPACE_NAME fromuser_segments

12:17:34 2where segment_name in ('NEW_ORDERS','ORDER_ITEMS') order by 1;

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

NEW_ORDERS TABLE PARTITION SYS_P346 TBS2

NEW_ORDERSTABLE PARTITION P2 TBS2

NEW_ORDERSTABLE PARTITION P3 TBS3

NEW_ORDERSTABLE PARTITION P4 TBS4

NEW_ORDERSTABLE PARTITION P1 TBS1

ORDER_ITEMSTABLE PARTITION P2 TBS2

ORDER_ITEMSTABLE PARTITION P3 TBS3

ORDER_ITEMSTABLE PARTITION P4 TBS4

ORDER_ITEMSTABLE PARTITION P1 TBS1

9 rows selected.

在子表中插入超出分区范围的数据：

12:17:49 SCOTT@ prod>insert into order_items values(105,805,200005,100);

1 row created.

12:19:55 SCOTT@ prod>commit;

Commit complete.

子表也自动扩展一个新的分区，且分区名和父表分区名相同：

12:19:57 SCOTT@prod>select segment_name,TABLESPACE_NAME fromuser_segments

12:20:05 2where segment_name in ('NEW_ORDERS','ORDER_ITEMS') order by 1;

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

NEW_ORDERSTABLE PARTITION P1 TBS1

NEW_ORDERSTABLE PARTITION P2 TBS2

NEW_ORDERSTABLE PARTITION P3 TBS3

NEW_ORDERS TABLE PARTITION SYS_P346 TBS2

NEW_ORDERSTABLE PARTITION P4 TBS4

ORDER_ITEMSTABLE PARTITION P4 TBS4

ORDER_ITEMSTABLE PARTITION P3 TBS3

ORDER_ITEMS TABLE PARTITION SYS_P346 TBS2

ORDER_ITEMSTABLE PARTITION P1 TBS1

ORDER_ITEMSTABLE 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 2PARTITION BY RANGE (sal)

12:44:27 3(PARTITION p1 VALUES LESS THAN (10000),

12:44:27 4PARTITION p2 VALUES LESS THAN (20000),

12:44:27 5PARTITION p3 VALUES LESS THAN (30000) );

Table created.

12:44:28 SCOTT@ prod>select segment_name,TABLESPACE_NAME fromuser_segments

12:44:35 2where segment_name='T1_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T1_PARTTABLE PARTITION P1 USERS

T1_PARTTABLE PARTITION P2 USERS

T1_PARTTABLE PARTITION P3 USERS

增加多个新分区到表中：

12:50:45 SCOTT@prod>ALTER TABLE t1_part ADD PARTITIONp4 VALUES LESS THAN (35000),PARTITION p5 VALUES LESS THAN (40000);

Table altered.

12:51:07 SCOTT@prod>select segment_name,TABLESPACE_NAME from user_segments

12:51:50 2where segment_name='T1_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T1_PARTTABLE PARTITION P1 USERS

T1_PARTTABLE PARTITION P2 USERS

T1_PARTTABLE PARTITION P3 USERS

T1_PART TABLE PARTITION P4 USERS

T1_PART TABLE PARTITION P5USERS

同样，只要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,TABLESPACE_NAME fromuser_segments

12:53:45 2where segment_name='T1_PART';

SEGMENT_NAMESEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME

-------------------- --------------------------------------

T1_PARTTABLE PARTITION P1 USERS

T1_PARTTABLE PARTITION P2 USERS

T1_PARTTABLE PARTITION P3 USERS

T1_PARTTABLE PARTITION P4 USERS

T1_PARTTABLE 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,TABLESPACE_NAMEfrom user_segments

12:54:24 2where segment_name='T1_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAME TABLESPACE_NAME

-------------------- --------------------------------------

T1_PARTTABLE PARTITION P1 USERS

T1_PARTTABLE PARTITION P2 USERS

T1_PARTTABLE 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),sal number(6))

12:58:11 2PARTITION BY RANGE (sal)

12:58:14 3(PARTITION p1 VALUES LESS THAN (10000),

12:58:18 4PARTITION p2 VALUES LESS THAN (20000),

12:58:21 5PARTITION p_max VALUES LESS THAN(MAXVALUE) );

Table created.

12:58:30 SCOTT@prod>select segment_name,TABLESPACE_NAME fromuser_segments

12:58:36 2where segment_name='T2_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T2_PARTTABLE PARTITION P1 USERS

T2_PARTTABLE PARTITION P2 USERS

T2_PARTTABLE PARTITION P_MAX USERS

Elapsed: 00:00:00.03

12:58:56 SCOTT@prod>ALTER TABLE t2_part

13:00:23 2SPLIT PARTITION p_max INTO

13:00:23 3(PARTITION p3 VALUES LESS THAN (25000),

13:00:23 4PARTITION p4 VALUES LESS THAN (30000),

13:00:23 5PARTITION p_max);

Table altered.

13:00:24 SCOTT@prod>select segment_name,TABLESPACE_NAME fromuser_segments

13:00:36 2where segment_name='T2_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T2_PARTTABLE PARTITION P1 USERS

T2_PARTTABLE PARTITION P2 USERS

T2_PARTTABLE PARTITION P3 USERS

T2_PARTTABLE PARTITION P4 USERS

T2_PARTTABLE 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),sal number(6))

13:02:12 2PARTITION BY RANGE (sal)

13:02:12 3(PARTITION p1 VALUES LESS THAN (10000),

13:02:12 4PARTITION p2 VALUES LESS THAN (20000),

13:02:12 5PARTITION p3 VALUES LESS THAN (30000),

13:02:12 6PARTITION p4 VALUES LESS THAN (40000),

13:02:12 7PARTITION p5 VALUES LESS THAN (50000),

13:02:12 8PARTITION p_max VALUES LESSTHAN (MAXVALUE) );

Table created.

13:02:14 SCOTT@prod>select segment_name,TABLESPACE_NAME fromuser_segments

13:02:21 2 where segment_name='T3_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T3_PARTTABLE PARTITION P_MAX USERS

T3_PARTTABLE PARTITION P5 USERS

T3_PARTTABLE PARTITION P4 USERS

T3_PARTTABLE PARTITION P3 USERS

T3_PARTTABLE PARTITION P2 USERS

T3_PARTTABLE 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,TABLESPACE_NAME fromuser_segments

13:06:44 2where segment_name='T3_PART';

SEGMENT_NAMESEGMENT_TYPEPARTITION_NAMetaBLESPACE_NAME

-------------------- --------------------------------------

T3_PARTTABLE PARTITION P1 USERS

T3_PARTTABLE PARTITION P_M23 USERS

T3_PART TABLE PARTITION P_MAX USERS

T3_PARTTABLE PARTITION P_MERG USERS

Oracle 12c Study之-- 分区表新特性

猜你在找的Oracle相关文章