目的
集計(AVG(sal) など)や結合(emp.deptno = dept.deptno など)を含むマテリアライズド・ビューを高速リフレッシュ可能にする。
環境
- OS: Oracle Enterprise Linux 5.8
- DB: Oracle Database 11g Release 2 (11.2.0.3)
マニュアル
- データ・ウェアハウス・ガイド
–> 9 基本的なマテリアライズド・ビュー
–> 集計を含むマテリアライズド・ビュー
集計を含むマテリアライズド・ビューの高速リフレッシュに関する制限
結合のみを含むマテリアライズド・ビュー
結合のみを含むマテリアライズド・ビューの高速リフレッシュに関する制限
DBMS_MVIEW.EXPLAIN_MVIEWプロシージャの使用
集計を含むマテリアライズド・ビュー
集計(AVG(sal) など)を含むマテリアライズド・ビューを高速リフレッシュ可能にする。
マニュアルから、集計関数(SUM, COUNT(*), AVG など)を含むマテリアライズド・ビューを高速リフレッシュ可能にするためにはいくつか制限がある。
ざっくり含める必要がある要件をまとめると以下。
マテリアライズド・ビュー・ログの要件
- マテリアライズド・ビューで参照される列をすべて含める
ROWIDおよびINCLUDING NEW VALUES句を指定- 表の更新を行う場合は
SEQUENCE句を指定
マテリアライズド・ビューの SELECT 文に含める要件
COUNT(*)を含めるAVG(expr)などの集計ごとに、対応するCOUNT(expr)およびSUM(expr)を含める。(集計関数によって要件は異なる)
例えば以下の SELECT を含むマテリアライズド・ビューを考える。
SELECT d.dname, AVG(e.sal) avg_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
高速リフレッシュ可能なマテリアライズド・ビューを作成する。
- マテリアライズド・ビュー・ログの作成
CREATE MATERIALIZED VIEW LOG 文を実行
SQL> CREATE MATERIALIZED VIEW LOG ON emp
WITH SEQUENCE, ROWID
(sal, deptno)
INCLUDING NEW VALUES;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW LOG ON dept
WITH SEQUENCE, ROWID
(deptno, dname)
INCLUDING NEW VALUES;
Materialized view log created.
- マテリアライズド・ビューの作成
CREATE MATERIALIZED VIEW 文を実行
SQL> CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT d.dname, AVG(e.sal) avg_sal,
COUNT(*) cnt, COUNT(e.sal) cnt_sal, SUM(e.sal) sum_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
Materialized view created.
高速リフレッシュ可能か確認する方法
DBMS_MVIEW.EXPLAIN_MVIEW プロシージャを使用することで簡単にマテリアライズド・ビューに関して次のことを確認できる。
- 高速リフレッシュ可能かどうか
- 実行できるクエリー・リライトのタイプ
- PCT リフレッシュ(パーティション単位での高速リフレッシュ)が可能かどうか(パーティション表でないと意味なし)
使い方は以下
$ORACLE_HOME/rdbms/admin/utlxmv.sqlを流してMV_CAPABILITIES_TABLE表を作成DBMS_MVIEW.EXPLAIN_MVIEWプロシージャの引数にマテリアライズド・ビュー名かマテリアライズド・ビューで使用している SELECT 文を与えて実行MV_CAPABILITIES_TABLE表を SELECT して確認- 再度
DBMS_MVIEW.EXPLAIN_MVIEWプロシージャを実行する場合はTRUNCATE TABLE MV_CAPABILITIES_TABLEで結果をクリア
先ほど作成したマテリアライズド・ビューで確認してみる。
SQL> @?/rdbms/admin/utlxmv.sql
Table created.
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('emp_dept_mv');
PL/SQL procedure successfully completed.
SQL> set lines 200
set pages 100
col capability_name for a30
col rel_text for a10
col msgtxt for a60
SQL> SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME POS REL_TEXT MSGTXT
------------------------------ --- ---------- ------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE N EMP relation is not a partitioned table
PCT_TABLE N DEPT relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE N EMP relation is not a partitioned table
PCT_TABLE_REWRITE N DEPT relation is not a partitioned table
16 rows selected.
possible 列が Y であることから高速リフレッシュ可能であることが分かる。(PCT* に関してはパーティション表ではないので無視)
例えば、以下のようにマテリアライズド・ビュー作成前に高速リフレッシュ可能か確認でき、可能でない場合は理由を表示してくれる。
SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;
Table truncated.
SQL> -- わざと COUNT(e.sal), SUM(e.sal) を外してみる
SQL> BEGIN
DBMS_MVIEW.EXPLAIN_MVIEW('
SELECT d.dname, AVG(e.sal) avg_sal,
COUNT(*) cnt
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname');
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME POS REL_TEXT MSGTXT
------------------------------ --- ---------- ------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N EMP relation is not a partitioned table
PCT_TABLE N DEPT relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT N AVG_SAL agg(expr) requires correspondng COUNT(expr) function
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE N EMP relation is not a partitioned table
PCT_TABLE_REWRITE N DEPT relation is not a partitioned table
16 rows selected.
高速リフレッシュが不可で、AVG_SAL 列に関して対応する COUNT(expr) が必要なことを指摘してくれる。
結合のみを含むマテリアライズド・ビュー
集計関数は含まずに結合(emp.deptno = dept.deptno など)のみを含むマテリアライズド・ビューを高速リフレッシュ可能にする。
マニュアルから、結合のみを含むマテリアライズド・ビューを高速リフレッシュ可能にするためにはいくつか制限がある。 ざっくり含める必要がある要件をまとめると以下。
マテリアライズド・ビュー・ログの要件
ROWID句を指定
マテリアライズド・ビューの SELECT 文に含める要件
FROM内の全ての表の ROWID を含める
例えば以下の SELECT を含むマテリアライズド・ビューを考える。
SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;
高速リフレッシュ可能なマテリアライズド・ビューを作成する。
- マテリアライズド・ビュー・ログの作成
CREATE MATERIALIZED VIEW LOG 文を実行
SQL> CREATE MATERIALIZED VIEW LOG ON emp
WITH ROWID;
Materialized view log created.
SQL> CREATE MATERIALIZED VIEW LOG ON dept
WITH ROWID;
Materialized view log created.
- マテリアライズド・ビューの作成
CREATE MATERIALIZED VIEW 文を実行
SQL> CREATE MATERIALIZED VIEW emp_dept_mv
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT e.ename, d.loc,
e.rowid e_rowid, d.rowid d_rowid
FROM emp e, dept d
WHERE e.deptno = d.deptno;
Materialized view created.
- 高速リフレッシュ可能か確認
先ほど行ったように DBMS_MVIEW.EXPLAIN_MVIEW プロシージャで確認する。
SQL> TRUNCATE TABLE MV_CAPABILITIES_TABLE;
Table truncated.
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW('emp_dept_mv');
PL/SQL procedure successfully completed.
SQL> SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME POS REL_TEXT MSGTXT
------------------------------ --- ---------- ------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE N EMP relation is not a partitioned table
PCT_TABLE N DEPT relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE N EMP relation is not a partitioned table
PCT_TABLE_REWRITE N DEPT relation is not a partitioned table
16 rows selected.
PCT* 以外は possible 列が Y になっているので高速リフレッシュ可能。
