12cで実装された MOVE ONLINE は索引もメンテナンスされてUNUSABLEにならず、DMLも待たされない。(Oracle Database)

この エントリ の続き。表題の通りで 12cで実装された MOVE ONLINEの方 がメリットがあります。

UPDATE INDEXES句による索引メンテナンスは表ロック(TMエンキュー)の
影響で並走するDMLが待たされて、かつ実行中に索引がUNUSABLEになる
瞬間が有るとしばちょう先生よりアドバイスされました。サンガツ！彡(ﾟ)(ﾟ)

下記のリンクもご覧ください。

しばちょう先生の試して納得！DBAへの道
第41回 [Oracle Database 12c] オンラインでのパーティション移動
http://www.oracle.com/technetwork/jp/database/articles/shibacho/index-2644371-ja.html

検証もしてみます。元データはこちら。

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

MOVE ONLINE してみますやで。彡(ﾟ)(ﾟ)

ALTER TABLE TBL_A MOVE PARTITION P201704 ONLINE;

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 ONLINE でも索引を使用できる状態に保てましたやで彡(^)(^)

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

Oracle Database SQL言語リファレンス 12cリリース2 (12.2) E72907-02
http://docs.oracle.com/cd/E82638_01/SQLRF/ALTER-TABLE.htm#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877__CJACIBIA
ONLINE
ONLINEを指定すると、表パーティションの移動中に、表パーティションに対するDML操作が許可されるようになります。