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

本ブログではマテリアライズドビューを作成するまでの手順はスキップします。（全ての手順は下のブログの手順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);

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

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', '?');

COMMIT設定をするためには同じデータベースにあるテーブルを元に作成できます。
作成すると、元のテーブルでトランザクションがCommitがある場合に自動的にリフレッシュします。

CREATE MATERIALIZED VIEW TEMP_DATA
  BUILD IMMEDIATE
  REFRESH COMPLETE ON COMMIT
  AS (SELECT * 
      FROM   admin.TEMP_DATA);

いろんな時間設定可能

# 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;

設定した後にリフレッシュするとエラーが出力されます。

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