※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