Oracle オンライン再定義でハマった話（マテリアライズドビューの裏仕様）その2

前回まで

こんにちは。前回はオンライン再定義を実施しようとしてハマったところまで書きました。
https://qiita.com/terry_carp/items/c12b0e771c2935c231fd
今回は、解決編です。

高速リフレッシュの謎を解く

まず、なんでこれまで経験しなかったほど、
データ同期に時間がかかったのかを調べることにしました。
既に運用しているマテビューとオンライン再定義で作成されたマテビューを
データ同期する際に実行されているSQLを見てみたところ、以下の違いがありました。

①既に運用しているマテビュー

　1. 溜まっているMLOGをSELECTする。
　2. 取得したレコードをベースにループし、
　　MLOGに記録されているDML_TYPE（UPDATE・INSERT・DELETE）に従い、
　　1件ずつUPDATE・INSERT・DELETEしていく。

②オンライン再定義で作成されたマテビュー

　1. 溜まっているMLOGをSELECTする。
　2. 取得したレコードに該当するマテビュー上のレコードを一旦DELETEする。
　3. MLOGに記録されているDML_TYPEがUPDATE・INSERTのものをMERGE文でUPSERTする。

時間がかかる理由

なるほど。。。
対象のテーブルはこれまでの数々のチューニングによりINDEXが乱立していたため、
マテビュー（当然コピーしているのでINDEXの数も同じ）からDELETEするのに時間がかかっていることが分かりました。
UPSERTもまあまあ時間がかかっていた。

高速リフレッシュの裏（？）仕様

それと、どうやら高速リフレッシュの仕様として、
「マテビューがリモートにある場合」は①の方法でリフレッシュ
「マテビューがローカルにある場合」は②の方法でリフレッシュ
することが分かりました。

今回は既存のテーブルを置き換えるということでマテビューがローカルにあったので、
②の方法でリフレッシュされたという訳です。
このカラクリが分かったので、
マテビューがローカルにあるか、リモートにあるか以外の事はすべて同じ条件で、
高速リフレッシュを試したところ、
②では50分程度かかったものが、①では1分ちょっと。マジか！！！

さて、どうするか？

上記のことが分かったので、どうにかこねくり回して①の方法実現したいところなのですが、
オンライン再定義パッケージはDB_LINK越しに使うことはできないので、
泣く泣く見送り。

ということで以下の手順を策定

①ローカルDBにループバックDB_LINKを張る。

②①で作成したDB_LINK越しに対象テーブルをマスタとしたマテビューを仮表として作成する。
（CREATE MATERIALIZED VIEWで作成、SELECT句に「ORDER BY CLIENT_ID」を付ける）

③②で作成したマテビューに元表と同じ制約・INDEXを作成する。
（制約やINDEX名は仮の名を付けておく）

④DBMS_MVIEW.REFRESHを5分毎に実行し、データ同期を行う。

（この間に一週間程度かけて、オンラインアプリで実行されるSQLを仮表に対して実行して実行計画に問題がないかを確認）

⑤仮表を実テーブル化する。
（DROP MATERIALIZED VIEWをPRESERVE TABLE句つきで実行）

⑥実テーブル化したテーブル・制約・INDEX名をリネーム

※⑤・⑥を実施するときはその後の関連作業があるため、サービス停止。

要はローカルにループバックDB_LINKを張って、
Oracleに「リモート越しにやってますよ～」と見せかけるということですね。

結果・・・うまくいった！

まとめ

オンライン再定義（と同様の事）を行う場合の手法とそれぞれのメリデメは以下の通りです。

オンライン再定義パッケージを使用

メリット

1. 作業者の手順が少なくて済む（これは結構デカいです）
2. 事前にパラレル指定をしておけば、DBMS_REDEFINITION.START_REDEF_TABLE（初回のデータ同期）をパラレル実行できる。
3. マスタテーブルにINDEXをあまり張っていない場合は、データ同期にも時間がかからない。

デメリット

マスタテーブルに大量レコードを保持している
かつ
大量のINDEXが張ってあるテーブルの場合、
データ同期（SYNC_INTERIM_TABLEまたはFINISH_REDEF_TABLE）の際に非常に時間がかかる恐れがある。
※個人的にはオンライン再定義したくなるテーブルはこれに当てはまることが多いと思っています。

ループバックDB_LINK経由でMVIEW作成→実テーブル化

メリット

大量レコードかつ大量のINDEXが張ってあるテーブルの場合でも、
高速リフレッシュ（DBMS_MVIEW.REFRESH）を短時間で完了できる可能性が高い。

デメリット

1. CREATE MATERIALIZED VIEW（初回のデータ同期）をパラレル実行できない。（DB_LINK経由なので）
2. 作業者の手順が多くなるので、作業者の負担が高い。
3. 作業ミスのポイントも増える。

まとめると、
「オンライン再定義パッケージ」はとても便利な機能ではあります。
しかし、オンライン再定義をしたくなるテーブルは、
長年の技術的負債が溜まっているような状態になっている場合が多いと思われ、
そのようなテーブルには向いていないという矛盾した課題を抱えています。
その場合は、「ループバックDB_LINK経由でMVIEW作成→実テーブル化」で代用することが可能。
ということになります。

長文、駄文失礼いたしました！