UNION ALLを使ったMVIEWの制約について [SQL・DDL]
1.はじめに
最近、MVIEWでUNION ALLを使った場合の制約について調査したので、備忘までメモを残しておきたい。
MVIEWとは実体のあるビューであり、ある断面のデータを複製してビューをあたかもローカルの表のようにして使う機能である。MVIEWは大規模なテーブルを集計する処理において、VIEW経由でアクセスされる度に計算するのではなく、あらかじめ集計結果を実体として保持しておくことにより、参照時に高価な処理を何度も行うことを回避できるメリットがある。
ソースとなる表が更新されることにより、レプリカであるMVIEWの更新が必要となるが、高速リフレッシュという機能を使うことにより、ソース側表の更新履歴(MLOGという)だけをMVIEWに反映することがで、差分更新が可能である。
高速リフレッシュが可能なクエリには制約がある。一般的に、結合や集計処理を含む複雑なクエリになるほど制約を意識する必要がある。また、MVIEWの定義にはDBLINKを利用できるため、(複数の)リモートDBからDBLINK経由でMVIEWを構成することができるが、これにも制約がある。実現したいビューがMVIEWで構成できるかどうかは、上記の複雑な制約の網をくぐり抜ける必要があるため、実機での確認がかかせない。
ここでは、スキーマ分割された表(定義は同一)を別データベースにMVIEWで1つにUNION ALLでまとめて参照したい、という要件について、実機でMVIEW の実現可否や運用上の制約事項を調べてみよう。。
2.検証モデル
検証のモデルとして、下図のような構成を考える。
- 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.
以上で準備は完了である。
3.検証
ここでは以下3つのシナリオで高速リフレッシュの動作検証を行う。
(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.
4.まとめ
今回の調査、検証を踏まえ、明らかになったことはは以下の通り。
- 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)
コメント 0