下記のブログではOracle DBをマテリアライズドビューを利用してデータを移行する方法をまとめました。
しかし、前回のブログの方法では移行先のデータベースに差分ができたら、手動コマンドを実行して最新状態に更新する必要がありました。
マテリアライズドビューには差分を自動で更新できることを含めていろんな設定があります。
本ブログではマテリアライズドビューのリフレッシュと関連があるオプションをまとめました。
リフレッシュ以外の他オプションの説明は下記のページをご参考ください。(Oracle Database 19)
AS SELECT文
AS SELECT文のみ作成すると、全ての設定がデフォルトになっているマテリアライズドビューを作成します。 リフレッシュとは関係ないですが、ここにオプションを追加して行きます。
CREATE MATERIALIZED VIEW [table_name]
AS
SELECT...;
- AS : マテリアライズド・ビューを定義するクエリを指定
- 実行結果がマテリアライズド・ビューに格納されて、有効なクエリーである必要がある
例
下記のようにデータベースリンクを利用して他のデータベースにあるテーブルのデータを持ってくるのもできます。
※ remote_site:データベースリンク名
CREATE MATERIALIZED VIEW TEMP_DATA
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
もし、同じデータベースが元になるマテリアライズドビューを作成したい時には下記のように実行します。
CREATE MATERIALIZED VIEW TEMP_DATA
AS (SELECT *
FROM admin.TEMP_DATA);
BUILD {IMMEDIATE | DEFERRED}
CREATE MATERIALIZED VIEW [table_name]
BUILD {IMMEDIATE | DEFERRED}
AS
SELECT...;
- BUILD {IMMEDIATE | DEFERRED} : マテリアライズドビューにいつデータを入れるかを決定
- IMMEDIATE
- デフォルト
- マテリアライズドビューの作成と一緒にデータを入れます。
- DEFERRED
- マテリアライズドビューを作成する時にデータを入れません。
- データを入れるためには別途のコマンド実行が必要
- IMMEDIATE
例(IMMEDIATE、DEFERRED)
IMMEDIATEはマテリアライズドビューを作成するタイミングにデータを入れるので、ビューを作成した後に確認してみるとデータが入っています。
# マテリアライズドビュー作成
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
# データ数の確認
select count(*) from TEMP_DATA;
# COUNT(*)
#----------
# 14000
DEFERREDで設定ですると、ビューを作成する時にデータを入れないです。
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD DEFERRED
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
select count(*) from TEMP_DATA;
# COUNT(*)
#----------
# 0
# データの最新状態に更新
EXEC DBMS_MVIEW.REFRESH('TEMP_DATA', '?');
# > PL/SQLプロシージャが正常に完了しました。
select count(*) from TEMP_DATA;
# COUNT(*)
#----------
# 14000
REFRESH {FAST | COMPLETE | FORCE}
CREATE MATERIALIZED VIEW [table_name]
BUILD {IMMEDIATE | DEFERRED}
REFRESH {FAST | COMPLETE | FORCE}
AS
SELECT...;
- REFRESH : 送信元のテーブルとの差分が発生した時にその内容を更新する作業
- FAST
- 移行元のデータベースにマテリアライズドビューログが必要。(MLOG$[table_name]・RUPD$[table_name])
- ログから更新レコード情報を確認して増分方式でデータをリフレッシュ
- 差分だけ更新するので、処理速度が早く
- 一部のテーブルに対して制限事項がある
- 移行元のデータベースにマテリアライズドビューログが必要。(MLOG$[table_name]・RUPD$[table_name])
- COMPLETE
- 既存のデータを削除して、送信元デーブルの全体のデータを再読み込んで更新する方法
- マテリアライズドビューログは必要ない
- FORCE
- デフォルト
- まずはFASTでリフレッシュを実行して、実行できない場合はCOMPLETE実行するオプション
- FAST
例(FAST、リフレッシュの手動実施する方法)
FAST設定
# ログ作成のために、テーブルにPRIMARY KEY(主キー)制約を追加
ALTER TABLE TEMP_DATA ADD CONSTRAINT pk_temp_0 PRIMARY KEY (COLA) USING INDEX;
# > Table TEMP_DATAが変更されました。
# マテリアライズドビューのログを作成
CREATE MATERIALIZED VIEW LOG ON TEMP_DATA;
# > Materialized view log TEMP_DATAは作成されました。
# 実際に MLOG$_TEMP_DATA と RUPD$_TEMP_DATA が作成されます。
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH FAST
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
COMPLETE と FORCE をする時にはログの設定はしなくても構いません。(FORCE にログがないと、FASTは失敗してCOMPLETEで実行する)
手動実施する方法もあります。
# FAST で実行(マテリアライズドビューログが必要)
EXEC DBMS_MVIEW.REFRESH('TEMP_DATA', 'f');
# COMPLETE で実行
EXEC DBMS_MVIEW.REFRESH('TEMP_DATA', 'c');
# FORCE で実行
EXEC DBMS_MVIEW.REFRESH('TEMP_DATA', '?');
ON {COMMIT | DEMAND | STATEMENT}
CREATE MATERIALIZED VIEW [table_name]
BUILD {IMMEDIATE | DEFERRED}
REFRESH {FAST | COMPLETE | FORCE}
ON {COMMIT | DEMAND | STATEMENT}
AS
SELECT...;
- ON {COMMIT | DEMAND | STATEMENT} : リフレッシュのトリガー設定
- COMMIT
- 元テーブルのトランザクションがコミットされたデータの変更によってリフレッシュを実行
- リモートテーブル(他DBにあるテーブル)を使用するマテリアライズド・ビューではサポート不可
- DEMAND
- デフォルト
- 手動や予約されたタスクによってリフレッシュを実行
- STATEMENT
- DML操作が実行される時にリフレッシュする
- マテリアライズド・ビューを作成するタイミングに作成して、その後は変更不可
- REFRESH FASTと一緒に設定する必要ある
- ON STATEMENTのリフレッシュの制限事項
- COMMIT
例(COMMIT)
作成すると、元のテーブルでトランザクションがCommitがある場合に自動的にリフレッシュします。
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH COMPLETE ON COMMIT
AS (SELECT *
FROM admin.TEMP_DATA);
START WITH {日時式} / NEXT {日時式}
CREATE MATERIALIZED VIEW [table_name]
BUILD {IMMEDIATE | DEFERRED}
REFRESH {FAST | COMPLETE | FORCE}
ON {COMMIT | DEMAND | STATEMENT}
START WITH {日時式}
NEXT {日時式}
AS
SELECT...;
ON DEMANDを指定している場合のみ設定可能で、START WITH・NEXTを指定すると、ON DEMANDより優先度が高くてON DEMANDの設定は無視します。
- START WITH
- 最初のリフレッシュされる日と時刻を指定(日時式)
- NEXT指定せずにSTART WITHのみ設定すると、指定された日と時刻に一回だけREFRESHする
- NEXT
- 自動でリフレッシュする間隔を計算するために日時式を指定
- START WITHを指定せずにNEXTのみ指定すると最初のリフレッシュはNEXTを参考して自動に設定
例(START WITH、NEXT)
いろんな時間設定可能
# 1分に一回データ更新する設定
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + INTERVAL '1' MINUTE
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
# 1時間に一回データ更新する設定
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TRUNC(SYSDATE, 'HH') + INTERVAL '1' HOUR
NEXT TRUNC(SYSDATE, 'HH') + INTERVAL '2' HOUR
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
# 毎日0時にデータ更新する設定
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TRUNC(SYSDATE) + 1
NEXT TRUNC(SYSDATE) + 1 + INTERVAL '1' DAY
AS
SELECT *
FROM admin.TEMP_DATA@remote_site;
# 毎日20時にデータ更新する設定
CREATE MATERIALIZED VIEW TEMP_DATA
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TRUNC(SYSDATE) + 20/24 -- 20時
NEXT TRUNC(SYSDATE) + 20/24 + INTERVAL '1' DAY
AS
SELECT *
FROM admin.TEMP_DATA@remote_site;
{ENABLE | DISABLE} ON QUERY COMPUTATION
CREATE MATERIALIZED VIEW [table_name]
BUILD {IMMEDIATE | DEFERRED}
REFRESH {FAST | COMPLETE | FORCE}
START WITH {日時式}
NEXT {日時式}
ON {COMMIT | DEMAND | STATEMENT}
[{ENABLE | DISABLE} QUERY REWRITE]
{ENABLE | DISABLE} ON QUERY COMPUTATION
AS
SELECT...;
- {ENABLE | DISABLE} ON QUERY COMPUTATION
- ENABLE
- リアルタイムのマテリアライズドビューを作成
- リアルタイムのマテリアライズド・ビューの制限事項
- DISABLE
- デフォルト
- 普通のマテリアライズドビューを作成
- ENABLE
NEVER REFRESH
CREATE MATERIALIZED VIEW [table_name]
NEVER REFRESH
AS
SELECT...;
- NEVER REFRESH
- リフレッシュを禁止する設定。
- データの更新ができなくなります。
- この設定をすると、DML操作を実行できる
例(NEVER REFRESH)
設定した後にリフレッシュするとエラーが出力されます。
CREATE MATERIALIZED VIEW TEMP_DATA
NEVER REFRESH
AS (SELECT *
FROM admin.TEMP_DATA@remote_site);
# リフレッシュ実施
EXEC DBMS_MVIEW.REFRESH('TEMP_DATA', '?');
# 次のコマンドの開始中にエラーが発生しました : 行 7 -
# BEGIN DBMS_MVIEW.REFRESH('TEMP_DATA', '?'); END;
# エラー・レポート -
# ORA-23538: NEVER REFRESHマテリアライズド・ビュー("TEMP_DATA")は明示的にリフレッシュできません
# ...
# 23538. 00000 - "cannot explicitly refresh a NEVER REFRESH materialized view (\"%s\")"
# *Cause: An attempt was made to explicitly refresh a NEVER REFRESH MV.
# *Action: Do not perform this refresh operation or remove the MV(s) from the list.
DML操作の実行テスト
# 設定してないビューの結果(実行できない)
エラー・レポート -
ORA-01732: このビューではデータ操作が無効です
ORA-06512: 行16
01732. 00000 - "data manipulation operation not legal on this view"
*Cause:
*Action:
# NEVER REFRESH を設定したビューの結果(DML操作可能)
# > PL/SQLプロシージャが正常に完了しました。