UNION ALLを使ったMVIEWの制約について [SQL・DDL]
１．はじめに
最近、MVIEWでUNION ALLを使った場合の制約について調査したので、備忘までメモを残しておきたい。
MVIEWとは実体のあるビューであり、ある断面のデータを複製してビューをあたかもローカルの表のようにして使う機能である。MVIEWは大規模なテーブルを集計する処理において、VIEW経由でアクセスされる度に計算するのではなく、あらかじめ集計結果を実体として保持しておくことにより、参照時に高価な処理を何度も行うことを回避できるメリットがある。
ソースとなる表が更新されることにより、レプリカであるMVIEWの更新が必要となるが、高速リフレッシュという機能を使うことにより、ソース側表の更新履歴（MLOGという）だけをMVIEWに反映することがで、差分更新が可能である。
高速リフレッシュが可能なクエリには制約がある。一般的に、結合や集計処理を含む複雑なクエリになるほど制約を意識する必要がある。また、MVIEWの定義にはDBLINKを利用できるため、（複数の）リモートDBからDBLINK経由でMVIEWを構成することができるが、これにも制約がある。実現したいビューがMVIEWで構成できるかどうかは、上記の複雑な制約の網をくぐり抜ける必要があるため、実機での確認がかかせない。
ここでは、スキーマ分割された表（定義は同一）を別データベースにMVIEWで１つにUNION ALLでまとめて参照したい、という要件について、実機でMVIEW の実現可否や運用上の制約事項を調べてみよう。。
２．検証モデル
検証のモデルとして、下図のような構成を考える。
- PDBは2つ（k3,k5)でその間にリモートMVIEW用のDBLINK（k5tok3）を構成する
- MVIEWのソースとなるPDB（k3）には3つのスキーマ（a,b,c）を作成し、それぞれemp表を作成する。データは主キーが重複しないように入れる
- EMP表には、それぞれ高速リフレッシュ用のMLOGを作成する
- MVIEWのターゲットであるPDB（k5）に3つのEMP表をUNION ALLしたMVIEWを作成する（MF_EMP_UNION）
この構成で特筆すべき点は2つある。1つはMLOGにROWIDを含めることである。ROWIDはソース表の行を特定する物理アドレスであり、これを更新ログに記録する。もう一つはMVIEWのカラム定義に、ROWIDとユニオンマーカ（ユニオンのソースとなる表を一位に特定するカラム）を含めることである。
以下に、手元のVirtual BoxのOracle19.11の環境を用いて、このモデルを実際に作成してみよう。まず表を作成する。以下の表定義をソースのPDBの3つのスキーマで実行する。
drop table emp cascade constraints; drop table dept cascade constraints; create table dept( deptno number(7,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno) ); create table emp( empno number(7,0), ename varchar2(10), job varchar2(9), mgr number(7,0), hiredate date, sal number(7,2), comm number(7,2), deptno number(7,0), constraint pk_emp primary key (empno), constraint fk_deptno foreign key (deptno) references dept (deptno) ); create index idx1_emp on emp ( deptno );
次にデータを挿入する。a、b、cのスキーマでempnoが100ずつずれるようにして挿入する。
conn b/b@k3
@creemp
insert into dept select
rownum+10 deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc
from dual connect by level <=10;
commit;
insert into emp select
rownum+100 empno,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,100)+100 mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,10)+10 deptno
from dual connect by level <=100;
commit;
exec dbms_stats.gather_table_stats('b','dept');
exec dbms_stats.gather_table_stats('b','emp');
conn c/c@k3
@creemp
insert into dept select
rownum+20 deptno,
dbms_random.string('u',14) dname,
dbms_random.string('u',13) loc
from dual connect by level <=10;
commit;
insert into emp select
rownum+200 empno,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,100)+200 mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,10)+20 deptno
from dual connect by level <=100;
commit;
exec dbms_stats.gather_table_stats('c','dept');
exec dbms_stats.gather_table_stats('c','emp');
挿入された行を各表毎3行ずつサンプルで確認する。
SQL> select empno, ename from a.emp where rownum <= 3
2 union all
3 select empno, ename from b.emp where rownum <= 3
4 union all
5* select empno, ename from c.emp where rownum <= 3;
EMPNO ENAME
________ _____________
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
201 LKCFXEGPPE
202 JKKFUPAHXM
203 VGMJJTAVUN
9 rows selected.
次にソース側PDB（k3）で各EMP表に対してMLOGを作成する。MLOGはROWIDを使って作成していることに留意する。
SQL> conn a/a@k3 Connected. SQL> create materialized view log on emp with rowid including new values; Materialized view log EMP created. SQL> conn b/b@k3 Connected. SQL> create materialized view log on emp with rowid including new values; Materialized view log EMP created. SQL> conn c/c@k3 Connected. SQL> create materialized view log on emp with rowid including new values; Materialized view log EMP created.
以上でソース側の準備が完了したため、リモート側のPDB（k5）でMVIEWを作成する。高速リフレッシュを使うため、refresh fastを指定し、各selectのカラムにrowid列とユニオンマーカとなるmarker列を含めていることに留意する。
SQL> conn f/f@k5 Connected. SQL> create materialized view mf_emp_union 2 build immediate refresh fast on demand as 3 select rowid "rowid", 1 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from a.emp@k5tok3 4 union all 5 select rowid "rowid", 2 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from b.emp@k5tok3 6 union all 7 select rowid "rowid", 3 marker, empno,ename,job,mgr,hiredate,sal,comm,deptno from c.emp@k5tok3 8* ; alter table mf_emp_union add constraint pk_mf_emp_union primary key (empno); Materialized view MF_EMP_UNION created. SQL> alter table mf_emp_union add constraint pk_mf_emp_union primary key (empno); Table MF_EMP_UNION altered.
MVIEWが作成され、同期されていることが確認する。
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME
________ _____________
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
201 LKCFXEGPPE
202 JKKFUPAHXM
203 VGMJJTAVUN
9 rows selected.
以上で準備は完了である。
３．検証
ここでは以下３つのシナリオで高速リフレッシュの動作検証を行う。
(1)c.EMPに全件UPDATE、c.EMPに1件INSERTを行い、高速リフレッシュ
(2)c.EMPをalter table moveし、高速リフレッシュ
(3)c.EMPをトランケートし、高速リフレッシュ
期待される結果は、(1)は正常に同期される。(2)(3)は高速リフレッシュがエラーとなり、復旧に完全リフレッシュが必要となるはずである。以下、それぞれについて結果を確認していこう。
(1)c.EMPに全件UPDATE、c.EMPに1件INSERTを行い、高速リフレッシュ
c.EMPにUPDATEと1件INSERTを行う。
SQL> conn c/c@k3 Connected. SQL> update emp set ename='ZZZ'; 100 rows updated. SQL> commit; Commit complete. SQL> insert into emp values(997, 'KING', 'PRESIDENT', null, sysdate, 5000, null, 21); 1 row inserted. SQL> commit; Commit complete.
ターゲットPDB（k5）にて、高速リフレッシュを実行する。高速リフレッシュ後に、MVIEWに正しくenameの更新、および1件INSERTされたことが確認できる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME
________ _____________
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
201 LKCFXEGPPE
202 JKKFUPAHXM
203 VGMJJTAVUN
9 rows selected.
SQL> select empno,ename from mf_emp_union where empno=997;
no rows selected
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f');
PL/SQL procedure successfully completed.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME
________ _____________
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
201 ZZZ
202 ZZZ
203 ZZZ
9 rows selected.
SQL> select empno,ename from mf_emp_union where empno=997;
EMPNO ENAME
________ ________
997 KING
SQL>
(2)c.EMPをalter table moveし、高速リフレッシュ
ソースPDB（k3）のc.EMPをalter table moveし、ROWIDを全て更新する。合わせて索引もリビルドする。これは典型的な表の断片化解消のシナリオである。
SQL> conn c/c@k3
Connected.
SQL> select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME ROWID
________ ________ _____________________
201 ZZZ AAASCEAASAAAFbtAAA
202 ZZZ AAASCEAASAAAFbtAAB
203 ZZZ AAASCEAASAAAFbtAAC
SQL> alter table emp move;
select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);
Table EMP altered.
SQL> select empno,ename,rowid from emp where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME ROWID
________ ________ _____________________
201 ZZZ AAASCiAASAAAFbzAAB
202 ZZZ AAASCiAASAAAFbzAAC
203 ZZZ AAASCiAASAAAFbzAAD
select index_name,status from user_indexes where index_name like '%EMP%';
SQL> select index_name,status from user_indexes where index_name like '%EMP%';
INDEX_NAME STATUS
_______________________ ___________
SYS_C_SNAP$_23PK_EMP VALID
I_MLOG$_EMP VALID
IDX1_EMP UNUSABLE
PK_EMP UNUSABLE
alter index pk_emp rebuild;
SQL> alter index pk_emp rebuild;
Index PK_EMP altered.
SQL> alter index idx1_emp rebuild;
Index IDX1_EMP altered.
SQL> select index_name,status from user_indexes where index_name like '%EMP%';
INDEX_NAME STATUS
_______________________ _________
SYS_C_SNAP$_23PK_EMP VALID
PK_EMP VALID
IDX1_EMP VALID
I_MLOG$_EMP VALID
ターゲットPDB（k5）にて、高速リフレッシュを実行する。高速リフレッシュはORA-12034エラーとなる。完全リフレッシュにより、正しい同期状態にすることができていることがわかる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename,rowid from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME ROWID
________ _____________ _____________________
1 WANOZRAMAP AAASBTAAWAAAAD7AAA
2 GMREPEQFIN AAASBTAAWAAAAD7AAB
3 SKWBNXLCNF AAASBTAAWAAAAD7AAC
101 GVXUEOOQJM AAASBTAAWAAAAD7ABk
102 XGLASIZISR AAASBTAAWAAAAD7ABl
103 UVODELRDCI AAASBTAAWAAAAD7ABm
201 ZZZ AAASBTAAWAAAAD9ACA
202 ZZZ AAASBTAAWAAAAD9ABk
203 ZZZ AAASBTAAWAAAAD9AC4
9 rows selected.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); --★高速リフレッシュ
Error starting at line : 1 in command -
BEGIN DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); END;
Error report -
ORA-12034: materialized view log on "C"."EMP" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1
12034. 0000 - "materialized view log on \"%s\".\"%s\" younger than last refresh"
*Cause: The materialized view log was younger than the last refresh.
*Action: A complete refresh is required before the next fast refresh.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'c'); --★完全リフレッシュ
PL/SQL procedure successfully completed.
SQL> select empno,ename,rowid from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME ROWID
________ _____________ _____________________
1 WANOZRAMAP AAASBTAAWAAAAD8ABo
2 GMREPEQFIN AAASBTAAWAAAAD8ABp
3 SKWBNXLCNF AAASBTAAWAAAAD8ABq
101 GVXUEOOQJM AAASBTAAWAAAAD8ABm
102 XGLASIZISR AAASBTAAWAAAAD8ABn
103 UVODELRDCI AAASBTAAWAAAAD9AAA
201 ZZZ AAASBTAAWAAAAD7ABr
202 ZZZ AAASBTAAWAAAAD7ABs
203 ZZZ AAASBTAAWAAAAD7ABt
9 rows selected.
(3)c.EMPをトランケートし、高速リフレッシュ
ソースPDB（k3）のc.EMPをトランケートで全件削除する。
SQL> conn c/c@k3 Connected. SQL> truncate table emp; Table EMP truncated.
ターゲットPDB（k5）にて、高速リフレッシュを実行する。高速リフレッシュはORA-12034エラーとなる。完全リフレッシュにより、正しい同期状態にすることができていることがわかる。
SQL> conn f/f@k5
Connected.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
201 ZZZ
202 ZZZ
203 ZZZ
9 rows selected.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); --★高速リフレッシュ
Error starting at line : 1 in command -
BEGIN DBMS_MVIEW.REFRESH('mf_emp_union', 'f'); END;
Error report -
ORA-12034: materialized view log on "C"."EMP" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1
12034. 0000 - "materialized view log on \"%s\".\"%s\" younger than last refresh"
*Cause: The materialized view log was younger than the last refresh.
*Action: A complete refresh is required before the next fast refresh.
SQL> EXECUTE DBMS_MVIEW.REFRESH('mf_emp_union', 'c'); --★完全リフレッシュ
PL/SQL procedure successfully completed.
SQL> select empno,ename from mf_emp_union where empno in (1,2,3,101,102,103,201,202,203);
EMPNO ENAME
________ _____________
1 WANOZRAMAP
2 GMREPEQFIN
3 SKWBNXLCNF
101 GVXUEOOQJM
102 XGLASIZISR
103 UVODELRDCI
6 rows selected.
４．まとめ
今回の調査、検証を踏まえ、明らかになったことはは以下の通り。
- UNION ALLで複数表をまとめたMVIEWを作成することは可能
- 高速リフレッシュを使うには、MLOGにROWIDを含め、かつ表の定義にROWIDおよびマーカーを追加する必要がある
- ソース表でROWIDを変更する操作（例えば、ソース表の断片化解消のためのMOVEやインポートなど）をすると、高速リフレッシュはエラーとなる（完全リフレッシュが必要）
- トランケートをすると、高速リフレッシュはエラーとなる（完全リフレッシュが必要）
MLOGおよびMVIEWにROWIDを含める必要があるという制約は、UNION ALLを使うMVIEWではソースの行をPKだけでは特定できないからと思われる（複数のソース表の主キーの重複が想定されるため）。
なお、今回検証結果は示していないが、MVIEW側でPKを定義すると、重複データが発生した場合にMVIEWのリフレッシュが一意制約違反で失敗するようにすることもできる。復旧のためには、重複データを特定しソース側で削除した後、完全リフレッシュが必要であることは言うまでもない。
参考
[1]Oracle Database データ・ウェアハウス・ガイド 19c
以上
2023-05-16 06:58
コメント(0)
