どうもsoboro1207です。
最近、寒くなったり暑くなったり地球が人を排除しようとしてる感が否めませんね。
今日も元気にSES！ｗ
はじめに
とある業務の更新処理でマテビュー…もとい正式名称マテアライズド・ビューというものに携わる機会がありました。
PL/SQLというよりOracleの機能なのですが触ったことのないものだったのでかなり苦戦しました。
今回はリフレッシュする際に必要なファイル、オプション周りでコケました。
しかも先輩方もよく知らないみたいだったので必死にドキュメントを読み漁りなんやかんやで実装に1週間半くらいかかった…。工数ェ…。
Qiitaというかこの広大なネットの海にそういう記事があまりないので備忘録としても後世のためにも書き残しておこうと思います。
マテビューとは
正式名称：マテリアライズド・ビュー
先ほどもちょっと触れたがOracleが提供している機能の一種で、
RDBのちょっといいとこ見てみたいって時に便利なもの。
ざっくり言えば普通の「ビュー」とは違い、実現(material)された「ビュー」
普通のビューが分からない方は勉強してください。
比較してみました。
| / | ビュー | マテビュー |
|---|---|---|
| CREATE | 複数の表を結合 | 複数の表を結合 |
| SELECT結果 | 実データは持たない | 実データを保持 |
| 実行可能なコマンド | 見るだけ(SELECT) | 殆ど使用できる ※1 |
| 参照元の変更 | 認知しない | 更新の度、リフレッシュが入る |
| カラム値の更新 | できない(作り直す必要がある) | 直接行える ※2 |
| 扱い方 | 結合結果の閲覧のみ | 結合結果を別の表として扱う |
※1: 普段のビューでは使えないコマンドはマテビューでも使えないが
それ以外であれば全て使える。
一般的なDLL、DMLは普通に流せる。試す機会がなかったがDCLも可能であろう。
マテビューの作成時にはいろいろ制約があるのでそこらへんは公式ドキュメントへ。
ただのビューであればSELECT結果とそう変わらないところを、マテビューにしておくと普通の表と同等の使い方ができる。
処理の流れ
これを毎日一回自動で走らせるジョブを作るというのが私の仕事だった。
Javaバッチで一日一回呼ばれる
↓
リフレッシュをかける
すること自体としてはこれで終わりです。
前後に呼び水・発火剤となるジョブがあるわけでもないので非常にシンプルでした。
いざ着手
要件定義から携わったのですが、制作にこぎつけるまで長かったですね。
もう一つ、メールを送信するジョブも担当だったのですがこちらも難敵で遅々として進みませんでした。
さて、それじゃあ張り切ってCREATE MATERIALIZED VIEW テーブル名する。
...といってもDDL等は先輩がもう流していたのでマテビュー自体は作ってあった。
この処理の大本命はリフレッシュすることなので
DBMS_MVIEWパッケージのREFRESHプロシージャを使用した。
最終的には以下のようなコードになった。
PROCEDURE MATELIALIZED_VIEW_REFRESH_PC
IS
BEGIN
-- 処理の本体
DBMS_MVIEW.REFRESH('テーブル名1, テーブル名2', 'ff');
EXCEPTION
WHEN OTHERS THEN
--例外処理
RAISE;
END MATELIALIZED_VIEW_REFRESH_PC;
最終的に出来たコードは規約で義務付けられたコメントや他の呼び出しで長くはなっているが部分的に見ればこの程度で収まる。
お気づきの方もいると思うが普段のビュー作成にMATERIALIZEDがくっついただけなので
AS句を使ってSELECT文で絞り込むこともできる。
なおかつ作成時に指定できるオプションが結構ある。
今回使用したのはmethodのみだが、この他にもたくさんある。
リフレッシュの種類
さっきからリフレッシュリフレッシュ言ってますが実は2種類あります。
完全リフレッシュと高速リフレッシュです。
完全リフレッシュはマテビューを一から作り直します。
データを完全に削除してからまた入れ直すので時間がかかります。
しかも完全リフレッシュ中にアクセスがあった場合、最悪サービスごと落ちます。
よって、今回は高速リフレッシュを使うことなりました。
高速リフレッシュは差分バックアップと同じように変更した部分だけ変更を加えます。
高速リフレッシュできない
先程のコードに'ff'というオプションがついていたと思います。
これは高速リフレッシュをするというのを明示するためのオプションです。
'a'と指定すると完全リフレッシュになります。
何も指定しないデフォルト状態で作成すると高速リフレッシュができるなら高速、できないなら完全リフレッシュと融通効かせてくれますが今回はそんな気遣いはいりません。
マテビューを作成するところでつまづいてはいられないので、プログラム本体との結合テストをしてエラーがあればさっさと潰していきたいところです。
いざテスト！
しかし...
行Nでエラーが発生しました。:
ORA-23413:
表"ユーザー"."TBL_XXXX"にはマテリアライズド・ビュー・ログはありません。
いやちょっとマテ茶
マテアライズド・ビュー・ログとは...???
マテアライズド・ビュー・ログ
よくよく調べてみると、高速リフレッシュの際にはこのマテアライズド・ビュー・ログなるものが必要であるとのこと。
元のテーブルに変更があるとこのマテアライズド・ビュー・ログにそれが書き込まれ、
リフレッシュする際にそれを見てマテビューを更新するらしい。
要は変更管理帳か…
ここは言われた通りに素直にマテビューログを作成する。
SQL> create materialized view log on TBL_XXXX;
これで高速リフレッシュができる！
と、思いきや....
- ログ作成の際に隠し属性であるROW_IDを指定しなきゃならない
- 新規の値がないと言われたのでそれも追加するべき(暗黙的によしなにしてくれるはずだが、明記しないとダメっぽい)
- フィルタ列が使えないと言われたので必要なカラムをすべて書く(SQLのGROUP BY句みたいに)
ここまでしてやっとこさちゃんと動くようになり、期待通りの動きをしてくれるようになった、、、。
これがそのソース(例)
マテビューログを作る時は参考にしてみてください。
SQL> create materialized view log on TBL_XXXX
2 with ROWID,sequence (フィルタ列1,フィルタ列2,フィルタ列3...)/*主キーは含んではならない*/
3 including new values; /* 新規の値をどうにかする */
まとめ
結局はマテビューで高速リフレッシュする時はマテビューログを作っておかないと無理!だということがわかりました。
最近流行りのウェッブ業界では役に立たないかも知れませんが古くからあるJava案件だと割と役に立つことが多いです。
書くことはなくても用語として話に出てきたりはするので知識あって損することはないと思います。
ちょっと面白みに欠けてたかもしれませんがこういう記事書いていこうと思います・・・
それではこの辺で。