表(パーティション)セグメントのMOVE(MOVE PARTITION)時に UPDATE INDEXES句 を付与すると索引がUNUSABLEにならない。(Oracle Database)

※5/10追記：MOVE ONLINEの方がメリットがあります。こちらもご覧ください。

表題の通りで、表(パーティション)セグメントのMOVE(MOVE PARTITION)時に
UPDATE INDEXES句 を付与すると索引がUNUSABLEにならないんやで彡(ﾟ)(ﾟ)

元データはこちら。

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN TABLE_NAME FORMAT A30;
COLUMN PARTITION_NAME FORMAT A30;
COLUMN INDEX_NAME FORMAT A30;
COLUMN SEGMENT_NAME FORMAT A20;
SELECT SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME, BYTES
  FROM USER_SEGMENTS
 WHERE SEGMENT_NAME LIKE 'TBL_A%'
 ORDER BY SEGMENT_NAME, PARTITION_NAME NULLS FIRST;

SEGMENT_NAME         SEGMENT_TYPE       PARTITION_NAME                      BYTES
-------------------- ------------------ ------------------------------ ----------
TBL_A                TABLE PARTITION    P201704                          27131904
TBL_A                TABLE PARTITION    P201705                           8388608
TBL_A                TABLE PARTITION    P201706                           8388608
TBL_A                TABLE PARTITION    P201707                           8388608
TBL_A                TABLE PARTITION    P201708                           8388608
TBL_A_I1             INDEX PARTITION    P201704                          16777216
TBL_A_I1             INDEX PARTITION    P201705                            917504
TBL_A_I1             INDEX PARTITION    P201706                            917504
TBL_A_I1             INDEX PARTITION    P201707                            917504
TBL_A_I1             INDEX PARTITION    P201708                            917504
TBL_A_I2             INDEX                                               65011712
TBL_A_PK             INDEX PARTITION    P201704                          24117248
TBL_A_PK             INDEX PARTITION    P201705                           3145728
TBL_A_PK             INDEX PARTITION    P201706                           3145728
TBL_A_PK             INDEX PARTITION    P201707                           3145728
TBL_A_PK             INDEX PARTITION    P201708                           3145728

SELECT INDEX_NAME, PARTITIONED, STATUS
  FROM USER_INDEXES WHERE TABLE_NAME = 'TBL_A'
 ORDER BY INDEX_NAME;

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
TBL_A_I1                       YES N/A
TBL_A_I2                       NO  VALID
TBL_A_PK                       YES N/A

SELECT INDEX_NAME, PARTITION_NAME, STATUS
  FROM USER_IND_PARTITIONS
 WHERE INDEX_NAME LIKE 'TBL_A%'
 ORDER BY INDEX_NAME, PARTITION_NAME;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TBL_A_I1                       P201704                        USABLE
TBL_A_I1                       P201705                        USABLE
TBL_A_I1                       P201706                        USABLE
TBL_A_I1                       P201707                        USABLE
TBL_A_I1                       P201708                        USABLE
TBL_A_PK                       P201704                        USABLE
TBL_A_PK                       P201705                        USABLE
TBL_A_PK                       P201706                        USABLE
TBL_A_PK                       P201707                        USABLE
TBL_A_PK                       P201708                        USABLE

まずは UPDATE INDEXES が無いパターン彡(ﾟ)(ﾟ)
表セグメントをMOVEした結果、索引は使用不可(UNUSABLE)になってしまいます。

ALTER TABLE TBL_A MOVE PARTITION P201704;

Table altered.

SELECT INDEX_NAME, PARTITIONED, STATUS
  FROM USER_INDEXES WHERE TABLE_NAME = 'TBL_A'
 ORDER BY INDEX_NAME;

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
TBL_A_I1                       YES N/A
TBL_A_I2                       NO  UNUSABLE ★索引使用不可(UNUSABLE)
TBL_A_PK                       YES N/A

SELECT INDEX_NAME, PARTITION_NAME, STATUS
  FROM USER_IND_PARTITIONS
 WHERE INDEX_NAME LIKE 'TBL_A%'
 ORDER BY INDEX_NAME, PARTITION_NAME;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TBL_A_I1                       P201704                        UNUSABLE ★索引使用不可(UNUSABLE)
TBL_A_I1                       P201705                        USABLE
TBL_A_I1                       P201706                        USABLE
TBL_A_I1                       P201707                        USABLE
TBL_A_I1                       P201708                        USABLE
TBL_A_PK                       P201704                        UNUSABLE ★索引使用不可(UNUSABLE)
TBL_A_PK                       P201705                        USABLE
TBL_A_PK                       P201706                        USABLE
TBL_A_PK                       P201707                        USABLE
TBL_A_PK                       P201708                        USABLE

そこで上記の MOVE文に UPDATE INDEXES を付与すると……彡(ﾟ)(ﾟ)

ALTER TABLE TBL_A MOVE PARTITION P201704 UPDATE INDEXES;

Table altered.

SELECT INDEX_NAME, PARTITIONED, STATUS
  FROM USER_INDEXES WHERE TABLE_NAME = 'TBL_A'
 ORDER BY INDEX_NAME;

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
TBL_A_I1                       YES N/A
TBL_A_I2                       NO  VALID ★VALIDのまま
TBL_A_PK                       YES N/A

SELECT INDEX_NAME, PARTITION_NAME, STATUS
  FROM USER_IND_PARTITIONS
 WHERE INDEX_NAME LIKE 'TBL_A%'
 ORDER BY INDEX_NAME, PARTITION_NAME;

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
TBL_A_I1                       P201704                        USABLE　★USABLEのまま
TBL_A_I1                       P201705                        USABLE
TBL_A_I1                       P201706                        USABLE
TBL_A_I1                       P201707                        USABLE
TBL_A_I1                       P201708                        USABLE
TBL_A_PK                       P201704                        USABLE　★USABLEのまま
TBL_A_PK                       P201705                        USABLE
TBL_A_PK                       P201706                        USABLE
TBL_A_PK                       P201707                        USABLE
TBL_A_PK                       P201708                        USABLE

索引を使用できる状態に保ちつつ、表セグメントをMOVEできたやで彡(^)(^)

マニュアルはこちら彡(ﾟ)(ﾟ)

• Oracle Database VLDBおよびパーティショニング・ガイド 12c リリース2 (12.2) E85255-01
  
  パーティションでサポートされているメンテナンス操作
  
  索引の自動更新
  
  http://docs.oracle.com/cd/E82638_01/VLDBG/maintenance-partition-can-be-performed.htm#GUID-1D59BD49-CD86-4BFE-9099-D3B8D7FD932A