正式にはマテリアライズド・ビュー（MATERIALIZED VIEW）と呼称します。VIEWと異なり、実際にデータを保持しています。VIEWではパフォーマンスに耐えられないような状況であってもマテビューで検索パフォーマンスを上げる事ができます。マテビューについては細かい利用制限や、注意点などあって実際の開発案件に適用するには事前にいろいろテストが必要です。マテビューのリフレッシュには大きく以下２つありますが、今回は必要最低限と思われる範囲にてそれぞれ記載してみました。
- 完全リフレッシュ refresh complete版
- リフレッシュ用プロシージャがコールされたタイミングで全件をリフレッシュします。バッチ処理などでよく利用します。
- 高速リフレッシュ （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です。）
マテビューのリフレッシュ
マテビューのリフレッシュのタイミング（リフレッシュモード）は大きく２つあります。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 ;
