RDS for Oracle マテリアライズドビューのリフレッシュ関連のオプションを調べてみた

RDS for Oracle マテリアライズドビューのリフレッシュ関連のオプションを調べてみた

Clock Icon2024.06.21 05:04

下記のブログではOracle DBをマテリアライズドビューを利用してデータを移行する方法をまとめました。

しかし、前回のブログの方法では移行先のデータベースに差分ができたら、手動コマンドを実行して最新状態に更新する必要がありました。
マテリアライズドビューには差分を自動で更新できることを含めていろんな設定があります。
本ブログではマテリアライズドビューのリフレッシュと関連があるオプションをまとめました。
リフレッシュ以外の他オプションの説明は下記のページをご参考ください。(Oracle Database 19)

AS SELECT文

AS SELECT文のみ作成すると、全ての設定がデフォルトになっているマテリアライズドビューを作成します。 リフレッシュとは関係ないですが、ここにオプションを追加して行きます。

CREATE MATERIALIZED VIEW [table_name]
  AS 
    SELECT...; 
  • AS : マテリアライズド・ビューを定義するクエリを指定
    • 実行結果がマテリアライズド・ビューに格納されて、有効なクエリーである必要がある
本ブログではマテリアライズドビューを作成するまでの手順はスキップします。(全ての手順は下のブログの手順3までは省略します。)

下記のようにデータベースリンクを利用して他のデータベースにあるテーブルのデータを持ってくるのもできます。
※ 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、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])
        • ログから更新レコード情報を確認して増分方式でデータをリフレッシュ
      • 差分だけ更新するので、処理速度が早く
      • 一部のテーブルに対して制限事項がある
    • COMPLETE
      • 既存のデータを削除して、送信元デーブルの全体のデータを再読み込んで更新する方法
      • マテリアライズドビューログは必要ない
    • FORCE
      • デフォルト
      • まずはFASTでリフレッシュを実行して、実行できない場合はCOMPLETE実行するオプション
例(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...; 

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プロシージャが正常に完了しました。

この記事をシェアする

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.