[oracle] UNION ALLを含むマテリアライズド・ビューの高速リフレッシュ

基本的にunion allのクエリでマテビューの高速リフレッシュはできない
しかし例外的に回避する手段がある

要件

union allするクエリのマテビューで高速リフレッシュする

リファレンス

5.3.8.7 UNION ALLを含むマテリアライズド・ビューの高速リフレッシュに関する制限
UNION ALL集合演算子を含むマテリアライズド・ビューは、次の条件が満たされる場合に、REFRESH FASTオプションをサポートします。

定義問合せの最上位レベルにUNION ALL演算子を含める必要があります。

UNION ALL演算子は、1つの例外を除き、副問合せ内に埋め込むことはできません。この例外とは、定義問合せの形式がSELECT * FROM(UNION ALLを含むビューまたは副問合せ)の場合に、UNION ALLを副問合せのFROM句に指定できるということです。この例を次に示します。

CREATE VIEW view_with_unionall AS
(SELECT c.rowid crid, c.cust_id, 2 umarker
FROM customers c WHERE c.cust_last_name = 'Smith'
UNION ALL
SELECT c.rowid crid, c.cust_id, 3 umarker
FROM customers c WHERE c.cust_last_name = 'Jones');

CREATE MATERIALIZED VIEW unionall_inside_view_mv
REFRESH FAST ON DEMAND AS
SELECT * FROM view_with_unionall;

つまり、union allするクエリでビューを作って、そのビューに対してselect *するマテビューを作成すれば高速リフレッシュ可能になる

手順

適当なテーブルを作成

• t1, t2
• union all前提のためカラムを合わせる

create table t1 ( c1 number, c2 number, c3 number );
create table t2 ( c1 number, c2 number, c3 number );

MLOG(マテリアライズド・ビュー・ログ)作成

CREATE MATERIALIZED VIEW LOG ON T1 WITH SEQUENCE, ROWID( c1, c2, c3 ), COMMIT SCN INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON T2 WITH SEQUENCE, ROWID( c1, c2, c3 ), COMMIT SCN INCLUDING NEW VALUES;

ビュー作成

• rowidに列別名が必要
• union allする各select文で別リテラルのmarkerが必要

create view v1 as (
    SELECT t.c1, t.c2, t.c3, t.rowid rowid, 1 MARKER FROM t1 t 
    UNION ALL
    SELECT t.c1, t.c2, t.c3, t.rowid rowid, 2 MARKER FROM t2 t

マテビュー作成

create materialized view mv1 REFRESH FAST on commit AS select * from v1;

MV_CAPABILITIES_TABLEチェック

utlxmv.sql実行

@?/rdbms/admin/utlxmv

MV_CAPABILITIES_TABLE更新

TRUNCATE TABLE MV_CAPABILITIES_TABLE;
exec DBMS_MVIEW.EXPLAIN_MVIEW('MV1');

MV_CAPABILITIES_TABLE確認

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
;

REFRESH_FASTがYなら高速リフレッシュ可

			
CAPABILITY_NAME	POSSIBLE	REL_TEXT	MSGTXT
PCT	N		
REFRESH_COMPLETE	Y		
REFRESH_FAST	Y	SCN base	
REWRITE	N		
PCT_TABLE	N	V1	表示された表がパーティション化されていません。
REFRESH_FAST_AFTER_INSERT	Y		
REFRESH_FAST_AFTER_ONETAB_DML	Y		
REFRESH_FAST_AFTER_ANY_DML	Y		
REFRESH_FAST_PCT	N		問合せに名前付きビューが含まれていると、PCT FAST REFRESHはできません
REWRITE_FULL_TEXT_MATCH	N		問合せのリライトはマテリアライズド・ビューではできません
REWRITE_PARTIAL_TEXT_MATCH	N		問合せのリライトはマテリアライズド・ビューではできません
REWRITE_GENERAL	N		問合せのリライトはマテリアライズド・ビューではできません
REWRITE_PCT	N		通常のリライトが不可能か、すべてのディテール表でPCTが使用不可能です。
PCT_TABLE_REWRITE	N	V1	表示された表がパーティション化されていません。

動作確認

insert into t1 values( 1, 1, 1 ) ;
commit;
select * from mv1 ;

commitしたレコードがmv1で確認できればOK

再実行用SQL

-- テーブル削除
drop table t1;
drop table t2;

-- MLOG削除
DROP MATERIALIZED VIEW LOG ON t1;
DROP MATERIALIZED VIEW LOG ON t2;

-- ビュー削除
drop view v1;

-- マテビュー削除
drop materialized view mv1;