自身の備忘を兼ねて記載を行っています。
「とりあえず動いた」程度のソースなどもございますので参考程度にブラシアップ頂けると幸いです。
また、誤りやもっとよいコーディングやきれいな書き方があるなどご指摘頂けるととてもうれしいです。
今回のお題
Oracleのマテリアライズドビューについてと利用していて私が良くやらかしてしまう失敗を戒めを込めて晒す
環境
| OS | データベース | バージョン | エディション | 備考 |
|---|---|---|---|---|
| CentOS release 6.9 | Oracle Database 12c | 12.2.0.1.0 | Standard Edition | 64bit |
マテリアライズドビューとは
リレーショナルデータベースで、テーブルからの検索結果であるビューにある程度の永続性を持たせ、参照するごとに再検索しなくてもいいようにしたもの。あるビューを頻繁に参照する場合に、毎回検索処理を実行しなくてよくなるため性能が向上する。
ソース：e-Words
つまりは、とあるタイミングの検索結果を保存しておくことで、大量のデータであっても比較的高速にデータを取得することが出来る機能です。
失敗までの下準備
マテリアライズドビュー(以降、マテビュー)は上記で説明しているように検索結果を保存しておくため、いつかは古いデータになってしまいます。
そこで用意されているのが、リフレッシュ(更新)で２種類の方法があります。
リフレッシュの種類
- 完全リフレッシュ
- 高速リフレッシュ
まぁ、名前の響きからお察し...かとは思いますが、
完全リフレッシュ
マテビュー側のデータを一度クリアして、再度条件を基に全データを取得し直します。
もちろん全データを取得し直すため、やや低速となります。
高速リフレッシュ
こちらのリフレッシュ方式は、いわゆる差分更新です。
INSERTやUPDATE、DELETEが行われたログを基にピンポイントで更新を行います。
その為、完全リフレッシュに比べ高速になります。
(更新量によっては完全リフレッシュの方が早いケースもありますが・・・)
ただ、ログを基にするため、「マテアライズドビューログ」というものを作成してあげる必要があります。
マテビューの作り方
さて失敗に向けてマテビューを作成していきたいと思います。
まずは、元となるテーブルです。
M_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000077 | 大阪 うご子 | 1993/01/14 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 2000/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 2002/08/01 | 技術部システム開発課 |
まぁこんなデータならマテビューにするまでもないのですが、一応例題ということでお許しを
次に、マテビューをつくります。
今回は高速リフレッシュをさせるためマテビューログから作成します。
-- マテビューログ
CREATE MATERIALIZED VIEW LOG ON M_社員;
-- マテビュー
CREATE MATERIALIZED VIEW MV_社員 REFRESH FAST ON DEMAND AS SELECT * FROM M_社員@DBLINK_VIEW;
一応、簡単にSQLを説明
マテビューログ
| SQL文 | 説明 |
|---|---|
| CREATE | これは言わずもがなですよね。作成命令句です。 |
| MATERIALIZED VIEW LOG | マテビューログを作成するための指定句です。 |
| ON M_社員 | これはどのテーブルを元となるテーブルとするかを指定しています。 |
マテビュー
| SQL文 | 説明 |
|---|---|
| CREATE | こちらも言わずもがな。 |
| MATERIALIZED VIEW | マテビューを作成するための指定句です。 |
| MV_社員 | マテビューの名前です。この名前でアクセスできるようになります。 |
| REFRESH FAST | これが高速リフレッシュのオプションです。 |
| ON DEMAND | 任意で更新をしないとリフレッシュしないというオプションです。 元テーブルがコミットされる度にリフレッシュを行う ON COMMITというオプションもあります。 |
| AS SELECT * FROM ... | これは保存させたい検索結果を返すSELECT文です。今回はシンプルに全データにしました。 |
詳しくは、やっぱり頼りになる Database SQL言語リファレンス
さて、実際に見てみましょう。
SELECT * FROM MV_社員;
結果
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000077 | 大阪 うご子 | 1993/01/14 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 2000/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 2002/08/01 | 技術部システム開発課 |
無事確認が取れました。
これで、マテビューの準備は完了です。
やらかしへの歩み
さぁ、粛々とやらかしへ近づいてきました。
マテビューを作成したので、次は元テーブルを更新してリフレッシュの動作を確認してみましょう。
M_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000077 | 大阪 うご子 | 1993/01/14 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 1997/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 1997/08/01 | 技術部システム開発課 |
| 0000260 | 来年 のび雄 | 1998/02/02 | 総務部 |
総務部が一人では辛いとのことで新人さんに入社頂きました。
では、マテビューを確認してみましょう。
MV_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000077 | 大阪 うご子 | 1993/01/14 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 1997/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 1997/08/01 | 技術部システム開発課 |
リフレッシュを掛けていないのでまだ、前の状態のままですね。
では、リフレッシュを行います。
BEGIN
DBMS_MVIEW.REFRESH('MV_社員', 'f');
END;
DBMS_MVIEWパッケージのREFRESHプロシージャを利用してリフレッシュを行っています。
パラメータは、マテビュー名と高速リフレッシュ(f)を指定しています。(完全リフレッシュの場合は(c)を指定)
もう一度確認してみます。
MV_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000077 | 大阪 うご子 | 1993/01/14 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 1997/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 1997/08/01 | 技術部システム開発課 |
| 0000260 | 来年 のび雄 | 1998/02/02 | 総務部 |
ちゃんと更新できました。
では次にUPDATEとDELETEです。
M_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 1997/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 1997/08/01 | 技術部システム開発課 |
| 0000260 | 来年 のび雄 | 1998/02/02 | 営業部第一課 |
お一方、ご結婚で退社(DELETE)され、総務部の新人さんが営業部に転属(UPDATE)になってしまいました。
リフレッシュを行いマテビューを確認すると
MV_社員
| 社員番号 | 名前 | 生年月日 | 所属 |
|---|---|---|---|
| 0000005 | 久井田 きじ雄 | 1985/04/01 | 総務部 |
| 0000062 | 東京 でき太 | 1992/07/25 | 営業部第一課 |
| 0000215 | 今日 天気 | 1997/12/05 | 技術部システム開発課 |
| 0000254 | 明日 雨子 | 1997/05/11 | 技術部システム開発課 |
| 0000259 | 昨日 嵐 | 1997/08/01 | 技術部システム開発課 |
| 0000260 | 来年 のび雄 | 1998/02/02 | 営業部第一課 |
ちゃんと更新されました。問題なさそうです。
リフレッシュは手動ではなくOracleのスケジューラを利用して定期的に高速リフレッシュを行っていきます。
そしてやらかし・・・
無事マテビューの更新が確認でき、スケジュール実行もうまくいっています。
すこぶる順調です。
データベースのメンテナンスの過程でテーブルデータを入れなおす必要が出たため、
バックアップをとり、Truncateを行いました。
マテビューを検索した結果も問題なさそう・・・
数日後
MV_社員が更新されていない。
スケジュールが無効になっているのかと思うが正常に動いていて完了しているみたい。
手動で高速リフレッシュを行うが、更新されない。
なぜだぁ～～～～
これが私のよくやるやらかしです。
ポイントは、ここ
バックアップをとり、Truncateを行いました。
ネタ晴らしをすると、高速リフレッシュはTruncateに対応していないのです。
もうちょっと正確にいうと、マテビューログにはTruncateの実行内容が記録されず、マテビュー側では情報の整合性が取れなくなってしまっているような状態になるのです。
解決方法
この状況を脱するのは簡単で、完全リフレッシュを行えばいいだけです。
BEGIN
DBMS_MVIEW.REFRESH('MV_社員', 'c');
END;
これでまた高速リフレッシュが行えるようになりました。
まとめ
マテアライズドビューの高速リフレッシュを利用しているいて、元テーブルのTruncateが必要となった場合は、セットで完全リフレッシュも行うようにしよう。