12c中维护多分区

12c中可以很方便的通过简单的语句同时操作多个分区，其中包括drop和truncate多个分区，多个分区合并成一个，一个拆分成多个。

DROP/TRUNCATE多个分区

从12c开始可以用一个单独的语句就能维护多个分区，比如drop或truncate多个分区，不过如果表含有域索引，则只能一次删除或截取一个分区。执行这些操作时，分区里的数据也会被清除，如果你想保留这些数据，那么需要用MERGE PARTITIONS方式。

除此之外，在这些操作的过程中，全局索引的维护可以延迟进行，这样即使删除了分区，全局索引仍然可用，这样保证了在业务高峰期我们也能进行分区的删除操作，而索引的维护可以放在低峰时期。

删除分区

在ALTER TABLE语句中可以通过DROP PARTITIONS或DROP SUBPARTITIONS来对range或list的多个分区或子分区进行删除。

drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

删除分区

ALTER TABLE t1
DROP PARTITIONS p1,p2;

Table altered.

检查分区和索引

SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ---------------------------------------------
T1         P3
T1         P4

SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

这里不能删除表的所有分区，如果只剩一个分区，那么就只能删除表。

当你删除多个分区时，全局和分区索引的维护跟你删除单个分区是一样的。删除分区时分区索引也会同时删除，全局索引需要重建。

截断分区

当使用ALTER TABLE … TRUNCATE PARTITION语句来清除表分区里的数据时，可以同时操作多个分区。

截断分区与删除分区类似

drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

截断分区

1
2
3

ALTER TABLE T1 TRUNCATE PARTITIONS P1,P2;

Table truncated.

检查分区和索引

SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1         P1
T1         P2
T1         P3
T1         P4


SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

全局索引必须要进行重建，除非之前指定了UPDATE INDEXES或UPDATE GLOBAL INDEXES。

索引维护

drop或truncate分区时，默认是采用异步维护全局索引的方式，然而UPDATE INDEXES语句同样需要带上，是为了向下兼容。

全局索引的维护是与DROP或TRUNCATE分区的维护独立开来的，从而不需要使全局索引失效。索引的维护是采用异步的方式，可以推迟到之后的某个时间点。推迟全局索引的维护是为了在不影响索引可用性的前提下来错峰进行，这样就大大加快了drop和truncate分区的速度和减少了操作过程中资源的消耗。

为了加快删除分区的速度，只会先维护索引的字典信息。这个功能在字典中维护数据对象编号的列表，那些被删除或阶段的对象所对应的索引由于失效而被忽略掉不进行维护。

索引的维护操作能自动的通过定时任务SYS.PMO_DEFERRED_GIDX_MAINT_JOB来清理所有的全局索引。默认是在每天凌晨2点进行，当然你也可以手动运行这个job。

DBMS_PART包提供了一个维护和管理分区对象的接口，作为优先使用异步维护全局索引的结果，全局索引可以包含指向那些已经不存在数据段的条目，这些陈旧的索引行在对表或索引上进行查询、DDL、DML操作时都不会导致任何影响正确性的问题。

DBMS_PART.CLEANUP_GIDX过程会确认和清除这些全局索引来保证存储的效率和性能。

局部全局索引优化

视图USER_INDEXES和USER_IND_PARTITIONS中新增了ORPHANED_ENTRIES字段，这个字段用来表示全局索引是否包含因为延迟索引维护产生的陈旧的条目。

字段可以为以下三种值：

YES:索引包含单独的条目
NO:索引不包含任何单独的条目
N/A：该属性不适用-针对分区索引或非分区表上的索引

你可以强制清理那些需要维护的索引：

DBMS_PART.CLEANUP_GIDX：收集那些需要被清理的全局索引，用来将这些索引恢复到正常状态

1	exec DBMS_PART.CLEANUP_GIDX('SCHEMA','INDEXNAME');

ALTER INDEX REBUILD [PARTITION]: 重建整个索引或索引分区，重建完之后的索引不会包含任何陈旧的条目

1	ALTER INDEX INDEXNAME REBUILD;

ALTER INDEX COALESCE [PARTITION] CLEANUP: 清理所有索引块中孤立的条目

1	ALTER INDEX INDEXNAME COALESCE CLEANUP;

合并多个分区

12c开始允许将多个分区或子分区合并到一个分区或子分区。

range分区/子分区

当合并多个range分区时，分区必须是相邻的，并按分区的边界值的升序指定。

新分区继承了原分区的最高边界

CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod_1 VALUES LESS THAN (100),
PARTITION prod_2 VALUES LESS THAN (200),
PARTITION prod_3 VALUES LESS THAN (300),
PARTITION prod_4 VALUES LESS THAN (400),
PARTITION prod_5 VALUES LESS THAN (500),
PARTITION prod_6 VALUES LESS THAN (600),
PARTITION prod_other VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;

将prod_list分区表中的三个分区合并成了一个，新分区中包含了之前三个分区代表的最早的商品ID，表示那些还未售出的商品。

你可以不用将每个分区都一一列出来，而是直接指定最小和最大的分区，中间通过TO关键字来表示范围

1	ALTER TABLE prod_list MERGE PARTITIONS prod_1 TO prod_3 INTO partition old;

LIST或系统分区/子分区

对于LIST分区来说，合并的时候就不需要相邻了，因为不需要分区是有序。另外不能将range表分区使用的TO语法来指定LIST分区。

当合并多个LIST分区，生成的分区包含的数据为原分区所有值的集合的合并。

DEFAULT的list分区与其他list分区合并产生的结果依然是DEFAULT分区。

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_CA VALUES('California'),
PARTITION sales_NY VALUES ('New York'),
PARTITION sales_NJ VALUES ('New Jersey'),
PARTITION sales_CT VALUES ('Connecticut'),
PARTITION sales_PA VALUES ('Pennsylvania'),
PARTITION sales_IL VALUES('Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list MERGE PARTITIONS sales_NY, sales_NJ, sales_CT INTO PARTITION TRI_STATE;

单分区分离成多个

跟前面的合并类似，只不过操作是刚好相反

Range分区

将一个range分区prod100分离成多个分区，用于表示prod25,prod50,prod75和prod100，prod100分区源自原来的prod100分区的最高边界

drop table prod_list
CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod100 VALUES LESS THAN (100),
PARTITION prod200 VALUES LESS THAN (200),
PARTITION prod300 VALUES LESS THAN (300),
PARTITION prodother VALUES LESS THAN (MAXVALUE)
);

alter table prod_list split partition prod100 into 
  (partition prod25 values less than (25),
   partition prod50 values less than (50),
   partition prod75 values less than (75),
   partition prod100);

换一个日期的例子，将表range_sales的sales_q1_2012分区分离成3个分区，用于表示第一季度的三个月份。

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));

   ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO
   (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
   PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
   PARTITION sales_mar_2012);

要将range分区表拆分成N个分区，需要在要分割分区的分区范围内指定(N-1)个分区键值。新分区的上界值需要按升序排序，第N个新分区的上界为被分割的分区的上界，第N个新分区的名称和物理属性可以在拆分时指定。

List分区

customer分区表被分割成3个

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list SPLIT PARTITION sales_east INTO
   (PARTITION NY values ('New York'),
    PARTITION FL values ('Florida'),
    PARTITION rest_sales_east);

将LIST分区拆分成N个分区时，需要指定（N-1）个分区键值，每个分区键值代表了（N-1）个分区需要插入的值。源分区余下的值则都被插入了第N个分区当中。前（N-1）个列值不能包含当前所有分区的值，因为这样的话第N个分区就为空了。同样新的（N-1）个分区不能包含任何当前分区不存在的值。

拆分多个分区的限制：

不适用hash分区
不能对索引组织表使用并行
如果表是索引组织表或则表上有本地域索引，那么你只能将分区拆分成2个新分区

12c新特性

#partition

12c中维护多分区

https://www.xbdba.com/2019/09/02/maintain-multiple-partitions/

作者

xbdba

发布于

2019年9月2日

许可协议

oracle buffer cache 上一篇