How to avoid Oracle global index rebuilt when moving partition

Question

Periodically, I need to detach partition from one table and attach to another table in different tablespace.

Pseudo flow:

• Loop every table and expired partition:

  1.1 Create a new target partition in target table

  1.2 Move partition to target tablespace

  1.3 Exchange source partition to a temp table

  1.4 Exchange temp table with target partition

  1.5 Drop source partition

• Rebuild global indexes in source table

• Rebuild global indexes in target table

Problem is, rebuilding index makes index temporary unavailable, which is no acceptable to support real-time application in my case.

Index rebuild is an expensive operation, one way to avoid that is by enforcing Asynchronous Global Index Maintenance, which makes global index orphan but still usable without any rebuild. However, orphan index can only be enabled in Partition Drop and Partition Truncate operations. In the flow above, global indexes are rendered UNUSABLE in Partition Move and Partition Exchange operations. Partition Drop in last statement can no longer enforce orphan index.

This is frustruating because I konw the partition will eventually be dropped, but still I am obliged to rebuild global index. Can you suggest anyway to avoid index rebuild?

Note:

• I could use UPDATE GLOBAL INDEXES clause in Partition Move and Partition Exchange clauses, but it is expensive and it would be called multiple times in loop.
• I could change global indexes to local indexes, but this will have greater performance impact on our application. Besides, unique index will not work (partition key is not involved).

Answer 1

One possible approach would be to copy the partition to be moved from the ACT in a TMP table (using CTAS in the required tablespace) and than

• exchange the TMP table with the partition of the HIST table and
• drop the partition in the ACT table.

This approach enables the Asynchronous Global Index Maintenance, but a somehow week point is that the move is done in two steps, so theoretically there could be queries that see both partition or no partition (depending on th eorder of the two steps). If this is a problem some additional care (such as locking the HIST table should be taken).

Here the example using INTERVAL PARTITONING (the table setup is below).

-- 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;

Table Setup

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;