oracle创建 删除分区索引,移动分区时如何避免重建Oracle全局索引

一种可能的方法是在TMP表中复制要从ACT移动的分区(在所需的表空间中使用CTAS)

>将TMP表与HIST表的分区交换

>删除ACT表中的分区.

这种方法启用了异步全局索引维护,但是在某种程度上,周点是移动分两步完成,因此理论上可能会有查询同时看到分区或没有分区(取决于两个步骤的顺序).如果这是一个问题,需要额外注意(例如锁定HIST表).

这里使用INTERVAL PARTITONING的示例(表格设置如下).

-- step 1 copy the partition to be moved from ACT table

create table tmp as

select * from t_act partition FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'));

-- step 2 allocate partition in HIST table

LOCK TABLE t_hist

PARTITION FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'))

IN SHARE MODE;

-- step 3 publish the copy in HIST table and DROP the ACT partition

ALTER TABLE t_hist

EXCHANGE PARTITION FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD'))

WITH TABLE tmp

INCLUDING INDEXES;

alter table t_act drop partition FOR (TO_DATE('2016-02-21', 'YYYY-MM-DD')) UPDATE INDEXES;

表设置

drop TABLE t_act;

CREATE TABLE t_act

(id number,

transaction_date DATE not null,

vc_pad VARCHAR2(100)

)

PARTITION BY RANGE (transaction_date)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

(

PARTITION P_01 VALUES LESS THAN (TO_DATE('2016-02-22', 'YYYY-MM-DD') ),

PARTITION P_02 VALUES LESS THAN (TO_DATE('2016-02-23', 'YYYY-MM-DD') )

);

ALTER TABLE t_act ADD CONSTRAINT t_act_pk PRIMARY KEY (id);

create index t_act_ix1 on t_act(transaction_date);

drop TABLE t_hist;

CREATE TABLE t_hist

(id number,

transaction_date DATE not null,

vc_pad VARCHAR2(100)

)

PARTITION BY RANGE (transaction_date)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

(

PARTITION P_01 VALUES LESS THAN (TO_DATE('2016-02-20', 'YYYY-MM-DD') ),

PARTITION P_02 VALUES LESS THAN (TO_DATE('2016-02-21', 'YYYY-MM-DD') )

);

insert into t_act

select rownum, TO_DATE('2016-02-21', 'YYYY-MM-DD') ,'TEST' from dual connect by level <= 100000;

commit;