【ORACLE】マテリアライズドビューを作成・リフレッシュ・削除するSQL

ここではORACLEデータベースで、マテリアライズドビュー(materialized view)を作成・リフレッシュ・削除する方法を紹介します。

マテリアライズドビューの確認方法は↓で紹介していますので参考にしてください。
＞＞【ORACLE】マテリアライズドビューの一覧を確認するSQL

マテリアライズドビューとは

マテリアライズドビューとは、あるテーブルから取得した検索結果を保持するビューです。

通常のビューはアクセスされる度にSQLを発行して定義したテーブルからデータを取得します。マテリアライズドビューはSELECTした結果を表として保持しているので定義したテーブルにアクセスすることはありません。

マテリアライズドビューはビューというよりも実体があるのでテーブルに近い存在です。

通常のビューについては↓で紹介していますので参考にしてください。
＞＞【ORACLE】ビューを作成・更新・削除するSQL

マテリアライズドビューを作成するSQL

マテリアライズドビューの作成は、CREATE MATERIALIZED VIEW文を使います。

マテリアライズドビューの作成や変更、削除には↓の権限が必要です。
　作成：CREATE ANY MATERIALIZED VIEW
　　　　CREATE TABLEまたはCREATE ANY TABLE
　変更：ALTER ANY MATERIALIZED VIEW
　削除：DROP ANY MATERIALIZED VIEW

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL

CREATE MATERIALIZED VIEW文の基本構文

CREATE MATERIALIZED VIEWの基本構文は次の通りです。

CREATE MATERIALIZED VIEW ビュー名
AS SELECT文;

CREATE MATERIALIZED VIEW文のSQLサンプル

CREATE MATERIALIZED VIEWのSQLサンプルを紹介します。

次のSQLでは、EMPテーブルからNAME列、AGE列、DEPT_CD列、DEPT_NAME列をMATERIALIZED VIEWとして参照できるようにしています。

CREATE MATERIALIZED VIEW mv_sales AS
SELECT
    a.name
    ,a.age
    ,a.dept_cd
    ,b.dept_name
FROM
    emp a
    inner join dept b on a.dept_cd = b.dept_cd
WHERE
    b.dept_name = 'sales'
;

マテリアライズドビューをリフレッシュする方法

MATERIALIZED VIEWはデフォルト[REFRESH ON DEMAND]状態ではリフレッシュされることはありません。

手動でリフレッシュするか、作成時に自動でリフレッシュするように指定する必要があります。

リフレッシュには2種類あり、完全リフレッシュと高速リフレッシュです。

完全リフレッシュはマテリアライズドビューの保持するデータを一度全て削除して、新たにSELECTし直してデータを保持させます。

高速リフレッシュは更新されたレコードだけ差分で更新します。高速リフレッシュを行うためにはマテリアライズドビューログを合わせて作成しておく必要があります。

手動でリフレッシュする方法

マテリアライズドビューを手動でリフレッシュするときはDBMS_MVIEWパッケージを使います。

exec DBMS_MVIEW.REFRESH('mv_sales','c');

1つ目の引数はマテリアライズドビューの名前、2つ目の引数はリフレッシュの種類を指定します。

リフレッシュの種類はcまたはCが完全リフレッシュ、fが高速リフレッシュ、?は強制リフレッシュ、影響を受けるパーティションをリフレッシュします。

自動で完全リフレッシュさせるSQL

マテリアライズドビューを自動でリフレッシュするには、CREATE文でREFRESH句を指定します。

次のSQLでは、完全リフレッシュを1日おきに実行しています。
「START WITH sysdate 」で初回のリフレッシュ日次を指定しています。「NEXT sysdate + 1」で初回以降のリフレッシュ間隔を指定しています。

CREATE MATERIALIZED VIEW mv_sales
REFRESH START WITH sysdate NEXT sysdate + 1
AS
SELECT
    a.name
    ,a.age
    ,a.dept_cd
    ,b.dept_name
FROM
    emp a
    inner join dept b on a.dept_cd = b.dept_cd
WHERE
    b.dept_name = 'sales'
;

自動で高速リフレッシュさせるSQL

マテリアライズドビューを自動で高速リフレッシュさせるには、
　・マテリアライズドビューログを作成します。
　・CREATE文のREFRESH句で「FAST」を指定します。

マテリアライズドビューログは、マテリアライズドビューに対して作成しておきます。

CREATE MATERIALIZED VIEW LOG ON emp;
CREATE MATERIALIZED VIEW LOG ON dept;

CREATE文では「REFRESH FAST」と指定し、高速リフレッシュされるようになります。START WITH sysdate 」で初回のリフレッシュ日次を指定しています。「NEXT sysdate + 1」で初回以降のリフレッシュ間隔を指定しています。

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST START WITH sysdate NEXT sysdate + 1
AS
SELECT
    a.name
    ,a.age
    ,a.dept_cd
    ,b.dept_name
FROM
    emp a
    inner join dept b on a.dept_cd = b.dept_cd
WHERE
    b.dept_name = 'sales'
;

データ更新に合わせて即時更新させるSQL

マテリアライズドビューで、定義したテーブルに更新があった場合、即時更新させるにはCREATE文で「ON COMMIT」を指定します。

即時更新する場合は高速リフレッシュ可能な状態で、定義したテーブルが同じデータベースにあることが前提となります。

CREATE MATERIALIZED VIEW mv_sales
REFRESH FAST ON COMMIT
AS
SELECT
    a.name
    ,a.age
    ,a.dept_cd
    ,b.dept_name
FROM
    emp a
    inner join dept b on a.dept_cd = b.dept_cd
WHERE
    b.dept_name = 'sales'
;

マテリアライズドビューを変更するSQL

マテリアライズドビューを変更するにはALTER文を使います。

次のSQLではリフレッシュ方法を高速リフレッシュに変更しています。

ALTER MATERIALIZED VIEW mv_sales REFRESH FAST; 

次のSQLではリフレッシュ間隔を更新しています。

ALTER MATERIALIZED VIEW mv_sales REFRESH NEXT sysdate+1; 

マテリアライズドビューを削除するSQL

マテリアライズドビューを削除するときはDROP文を使います。

DROP MATERIALIZED VIEW mv_sales;

マテリアライズドビューログもDROP文で削除できます。

DROP MATERIALIZED VIEW LOG ON emp;
DROP MATERIALIZED VIEW LOG ON dept;

まとめ

マテリアライズドビューの作成はCREATE MATERIALIZED VIEW文、変更はALTER文、削除はDROP文で行います。

リフレッシュは手動で行うか、作成時に自動で行うように指定する方法があります。

マテリアライズドビューを参照するにはオブジェクト権限を付与する必要があります。オブジェクト権限については↓で紹介していますので参考にしてください。
　・【ORACLE】オブジェクト権限を付与・取消するSQL
　・【ORACLE】オブジェクト権限を確認するSQL