oracle

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

正式にはマテリアライズド・ビュー(MATERIALIZED VIEW)と呼称します。VIEWと異なり、実際にデータを保持しています。VIEWではパフォーマンスに耐えられないような状況であってもマテビューで検索パフォーマンスを上げる事ができます。マテビューについては細かい利用制限や、注意点などあって実際の開発案件に適用するには事前にいろいろテストが必要です。マテビューのリフレッシュには大きく以下2つありますが、今回は必要最低限と思われる範囲にてそれぞれ記載してみました。

  1. 完全リフレッシュ refresh complete版
    • リフレッシュ用プロシージャがコールされたタイミングで全件をリフレッシュします。バッチ処理などでよく利用します。
  2. 高速リフレッシュ (refresh complete)版
    • 元テーブルが更新されcommitが入ったタイミングで、前回リフレッシュからの差分のみをリフレッシュします。元表への更新がリアルタイムで反映されます。

必要な権限

何れのマテビューを作成するにも、CREATE MATERIALIZED VIEW、CREATE TABLEシステム権限が必要です。(CREATE TABLEシステム権限は「RESOURCEロール」に含まれているからほとんどの場合すでに保有しているとおもいます。)権限がないのに作成を試みると『 ORA-01031: 権限が不足しています。 』が発生します。

権限付与DDL
grant CREATE MATERIALIZED VIEW to dabada
;

完全リフレッシュ refresh complete版 の作成

作成DDL
create materialized view prefecture_mmv
build deferred
refresh complete with rowid on demand
as
select p.prefid,p.name,p.name_r,r.name as region_name
from oy_prefecture p inner join oy_region r
on p.regin_id = r.region_id
;
作成確認SQL
select OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD
,FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE
from DBA_MVIEWS t
where t.MVIEW_NAME='PREFECTURE_MMV'
and t.OWNER='DABADA'
;
                                                        FAST
OWNER    MVIEW_NAME       REFRESH_MODE REFRESH_METHOD   REFRESHABLE  LAST_REFRESH_TYP LAST_REFRESH_DATE
-------- ---------------- ------------ ---------------- ------------ ---------------- -------------------
DABADA   PREFECTURE_MMV   DEMAND       COMPLETE         DIRLOAD_DML  NA

SQL>
SQL*Plus表示用
set lin 200
COLUMN OWNER FORMAT A8
COLUMN MVIEW_NAME FORMAT A16
COLUMN REFRESH_MODE FORMAT A12
COLUMN FAST_REFRESHABLE FORMAT A12
COLUMN FAST_REFRESHABLE HEADING 'FAST|REFRESHABLE'

これでマテビュー定義までは作成しましたが、上記はbuild deferredとしているので「中身」はまだ作成されていません。リフレッシュを行ってはじめて中身が作成されます。(build immediateとすれば同じタイミングで中身も作成されます。buildオプションは省略する事可能でデフォルトはimmediateです。)

マテビューのリフレッシュ

マテビューのリフレッシュのタイミング(リフレッシュモード)は大きく2つあります。ON COMMITは元になっているテーブルに対しcommitが行われたタイミング、ON DEMANDは別途リフレッシュ用のプロシージャを実行したタイミングで行われます。以下例です。

マテビューリフレッシュ(ON DEMAND)プロシージャの実行
execute dbms_mview.refresh('prefecture_mmv', method => 'c')
;
実行と確認例
SQL> execute dbms_mview.refresh('prefecture_mmv', method => 'c')

PL/SQLプロシージャが正常に完了しました。

SQL> select OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD
  2  ,FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE
  3  from DBA_MVIEWS t
  4  where t.MVIEW_NAME='PREFECTURE_MMV'
  5  and t.OWNER='DABADA'
  6  ;

                                                        FAST
OWNER    MVIEW_NAME       REFRESH_MODE REFRESH_METHOD   REFRESHABLE  LAST_REFRESH_TYP LAST_REFRESH_DATE
-------- ---------------- ------------ ---------------- ------------ ---------------- -------------------
DABADA   PREFECTURE_MMV   DEMAND       COMPLETE         DIRLOAD_DML  COMPLETE         2022-07-18 02:03:48

SQL>
マテビューの削除
drop materialized view prefecture_mmv
;

高速リフレッシュ (refresh first)版 の作成

高速リフレッシュは「差分更新」となるわけですが、その「差」を保持するためのログテーブルを事前に作成しておく必要があります。

ログテーブル作成DDL
-- 元表 oy_region 用のログテーブル作成
CREATE MATERIALIZED VIEW LOG ON oy_region
with ROWID 
;
-- 元表 OY_PREFECTURE 用のログテーブル作成
CREATE MATERIALIZED VIEW LOG ON OY_PREFECTURE
with ROWID
;
ログテーブル作成確認SQL
select t.LOG_OWNER,t.LOG_TABLE,t.MASTER,t.ROWIDS,t.PRIMARY_KEY,t.FILTER_COLUMNS,t.SEQUENCE,t.LAST_PURGE_DATE
from DBA_MVIEW_LOGS t 
where t.MASTER in ('OY_REGION','OY_PREFECTURE')
and t.LOG_OWNER ='DABADA'
order by t.MASTER
;
SQL> set lin 200
SQL> COLUMN LOG_OWNER FORMAT A8
SQL> COLUMN LOG_TABLE FORMAT A24
SQL> COLUMN MASTER FORMAT A16
SQL> COLUMN ROWIDS FORMAT A3
SQL> COLUMN PRIMARY_KEY FORMAT A3
SQL> COLUMN FILTER_COLUMNS FORMAT A3
SQL> COLUMN PRIMARY_KEY FORMAT A3
SQL> COLUMN SEQUENCE FORMAT A3
SQL>
SQL> select t.LOG_OWNER,t.LOG_TABLE,t.MASTER,t.ROWIDS,t.PRIMARY_KEY,t.FILTER_COLUMNS,t.SEQUENCE,t.LAST_PURGE_DATE
  2  from DBA_MVIEW_LOGS t
  3  where t.MASTER in ('OY_REGION','OY_PREFECTURE')
  4  and t.LOG_OWNER ='DABADA'
  5  order by t.MASTER
  6  ;

LOG_OWNE LOG_TABLE                MASTER           ROW PRI SEQ LAST_PURGE_DATE
-------- ------------------------ ---------------- --- --- --- -------------------
DABADA   MLOG$_OY_PREFECTURE      OY_PREFECTURE    YES NO  NO
DABADA   MLOG$_OY_REGION          OY_REGION        YES NO  NO

SQL>

元表の何れかで変更がされCOMMITのタイミングでマテビューをリフレッシュしたいので「ON COMMIT」としています

高速リフレッシュ版作成DDL
create materialized view prefecture_f_mmv
REFRESH FAST 
on commit
AS
select r.ROWID rrowid,p.ROWID prowid,p.prefid,p.name,p.name_r,r.name as region_name
from oy_prefecture p inner join oy_region r
on p.regin_id = r.region_id
;
作成確認SQL
select OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD
,FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE
from DBA_MVIEWS t
where t.MVIEW_NAME='PREFECTURE_F_MMV'
and t.OWNER='DABADA'
;
作成確認

高速リフレッシュ(差分更新)で作成した場合、createのタイミングで一旦データが作成されます。今後元表に更新がされた分が差分更新されていきます。

SQL> select OWNER,MVIEW_NAME,REFRESH_MODE,REFRESH_METHOD
  2  ,FAST_REFRESHABLE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE
  3  from DBA_MVIEWS t
  4  where t.MVIEW_NAME='PREFECTURE_F_MMV'
  5  and t.OWNER='DABADA'
  6  ;

                                                        FAST
OWNER    MVIEW_NAME       REFRESH_MODE REFRESH_METHOD   REFRESHABLE  LAST_REFRESH_TYP LAST_REFRESH_DATE
-------- ---------------- ------------ ---------------- ------------ ---------------- -------------------
DABADA   PREFECTURE_F_MMV COMMIT       FAST             DIRLOAD_DML  COMPLETE         2022-07-18 02:24:40

--
-- 再度ログテーブルを確認するとLAST_PURGE_DATEが更新されています。
-- 以降このカラムは元表の更新がマテビューへ反映される毎に更新されていきます
--
SQL> select t.LOG_OWNER,t.LOG_TABLE,t.MASTER,t.ROWIDS,t.PRIMARY_KEY,t.FILTER_COLUMNS,t.SEQUENCE,t.LAST_PURGE_DATE
  2  from DBA_MVIEW_LOGS t
  3  where t.MASTER in ('OY_REGION','OY_PREFECTURE')
  4  and t.LOG_OWNER ='DABADA'
  5  order by t.MASTER
  6  ;

LOG_OWNE LOG_TABLE                MASTER           ROW PRI FIL SEQ LAST_PURGE_DATE
-------- ------------------------ ---------------- --- --- --- --- -------------------
DABADA   MLOG$_OY_PREFECTURE      OY_PREFECTURE    YES NO  NO  NO  2022-07-18 02:43:29
DABADA   MLOG$_OY_REGION          OY_REGION        YES NO  NO  NO  2022-07-18 02:43:29

SQL>

削除する場合

-- ログテーブルの削除
drop MATERIALIZED VIEW LOG on oy_region
;
drop MATERIALIZED VIEW LOG on OY_PREFECTURE
;
-- マテビューの削除
drop materialized view prefecture_mmv
;
drop materialized view prefecture_f_mmv
;

DDL出力をしたい場合は以下の方法でファイル出力できます

スポンサーリンク
コピペで使う