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

※5/10追記：MOVE ONLINEの方がメリットがあります。[こちら](http://qiita.com/ora_gonsuke777/items/e70dad4041943a6c2c6e)もご覧ください。

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

元データはこちら。

```sql
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)になってしまいます。

```sql
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 を付与すると……彡(ﾟ)(ﾟ)

```sql
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<br>
パーティションでサポートされているメンテナンス操作<br>
索引の自動更新<br>
http://docs.oracle.com/cd/E82638_01/VLDBG/maintenance-partition-can-be-performed.htm#GUID-1D59BD49-CD86-4BFE-9099-D3B8D7FD932A