Oracleには、マテリアライズド・ビュー(略してマテビュー)というものがある。
単純に言って、テーブルのように実体を持ったビューのこと。
単なるビューは元となるテーブルを結合してみたり演算してみたりするものだが、使うたびに計算し直すのでパフォーマンス的に不利。
マテリアライズドビューは その計算結果を保持しておき、いちいち計算し直さない。
使う側からすれば、ただ単に「高速なビュー」という感じになる(更新頻度が少なく、参照頻度が高いデータの場合)。
|
|
最も単純な文法は、普通のcreate viewに対しmaterializedを加えるだけ。
create materialized view VW_TEST
as
select * from TBL_TEST;
しかし実際には色々なオプションが必要になる。
その前に、マテビューを作るには専用の権限が必要。
grant CREATE MATERIALIZED VIEW to ユーザー;
権限が無いと、以下のようなエラーが発生する。[2008-02-19]
SQL> create materialized view mv_emp
2 as
3 select * from emp;
select * from emp
*
行3でエラーが発生しました。:
ORA-01031: insufficient privileges
オプションの必要性の最大の理由は、「どのタイミングで計算し直すか」にある。
ビューの特徴は、その元となるテーブルが変わればビューの結果も変わること。
マテビューは計算結果を保持するので、テーブルが変わったらやはり計算し直す必要がある。
この計算タイミングにはリアルタイムと時刻指定があり、計算方法も毎回全データを計算し直す・変更があった箇所だけ計算し直すといった種類がある。
ただしOracleの制限により、ビューの内容によっては必ずしもどのパターンも選べるわけではない。
SQL> create materialized view MV_TEST
2 as
3 select
4 YYMM_GROUP,
5 SHOP_CODE,
6 sum(AMOUNT) SUM_AMOUNT
7 from TBL_TEST t
8 left join TBL_YYMM y
9 on y.YYMM_CODE=t.YYMM_CODE
10 where y.YYMM_GROUP is not null
11 group by YYMM_GROUP,SHOP_CODE
12 ;
from TBL_TEST t
*
行7でエラーが発生しました。:
ORA-00907: 右カッコがありません。
select部分だけをsql*plusから実行すれば当然ちゃんと動くし、単なるビューの作成なら問題なく作れるんだけど、マテビューだとなぜか「右カッコがない」という意味不明なエラーが発生する。括弧はちゃんと揃ってるやんけ!
どうも、left joinをしている時にwhereがあるのが気に食わないらしい。
この例でやりたい内容を考えると 素直にinner joinすればいいので、そう変えてやったらちゃんと作れた。
SQL> create materialized view MV_TEST 2 as 3 select 4 YYMM_GROUP, 5 SHOP_CODE, 6 sum(AMOUNT) SUM_AMOUNT 7 from TBL_TEST t 8 inner join TBL_YYMM y 9 on y.YYMM_CODE=t.YYMM_CODE 10 group by YYMM_GROUP,SHOP_CODE 11 ; マテリアライズド・ビューが作成されました。
あと、マテビューはテーブルと同じように実体を持つので、「CREATE TABLE」と同様にテーブルスペースや容量の指定が出来る。[2007-12-08]
→Oracle Database SQLリファレンスのCREATE
MATERIALIZED VIEW
マテビューを再計算する際の方法を指定する。[2007-12-08]
再計算方法 | 指定 | 概要 |
---|---|---|
完全リフレッシュ | refresh complete | 全データを再計算し直す。 |
高速リフレッシュ | refresh fast | 更新のあった箇所だけを再計算する。 |
デフォルト | refresh force | 高速リフレッシュ可能なら高速リフレッシュ、そうでないなら完全リフレッシュを実行する。 |
リフレッシュしない | never refresh | 再計算しない。手動でのリフレッシュ指示も無効。 |
リフレッシュ方法を指定しない場合はforceになる。
後からリフレッシュ方法を変えることも出来る。
SQL> alter materialized view MV_TEST refresh fast;
マテリアライズド・ビューが変更されました。
マテビューを計算し直す際に、全データを作り直す。データの変更箇所が少ないなら効率は悪いことになるが、ロジックは一番単純だ。
完全リフレッシュのマテビューを作る際には、「refresh complete」を指定する。個人的には「refresh all」の方が分かり易かったと思うけど…わたしゃ英語圏の人間じゃないからな〜
create materialized view MV_TEST
refresh complete
as
select
YYMM_GROUP,
SHOP_CODE,
sum(AMOUNT) SUM_AMOUNT
from TBL_TEST t
inner join TBL_YYMM y
on y.YYMM_CODE=t.YYMM_CODE
group by YYMM_GROUP,SHOP_CODE
;
マテビューを計算し直す際に、変更があった箇所だけを計算し直す。なので、効率はいいはず。
が、高速リフレッシュを行うにはOracleの制限(集計関数を使う際や表の結合の条件とかUNIONは対象外とか)が色々ある。
→Oracle Databaseデータ・ウェアハウス・ガイドの高速リフレッシュにおける制限
高速リフレッシュのマテビューを作る際には、「refresh fast」を指定する。
create materialized view MV_TEST
refresh fast
as
select
YYMM_GROUP,
SHOP_CODE,
sum(AMOUNT) SUM_AMOUNT
from TBL_TEST t
inner join TBL_YYMM y
on y.YYMM_CODE=t.YYMM_CODE
group by YYMM_GROUP,SHOP_CODE
;
しかし、それだけでは高速リフレッシュのマテビューを作ることはできない。
SQL> create materialized view MV_TEST
2 refresh fast
3 as
4 select
5 YYMM_GROUP,
6 SHOP_CODE,
7 sum(AMOUNT) SUM_AMOUNT
8 from TBL_TEST t
9 inner join TBL_YYMM y
10 on y.YYMM_CODE=t.YYMM_CODE
11 group by YYMM_GROUP,SHOP_CODE
12 ;
from TBL_TEST t
*
行8でエラーが発生しました。:
ORA-23413:
表"ユーザー"."TBL_YYMM"にはマテリアライズド・ビュー・ログはありません。
高速リフレッシュを指定するには、元テーブルに対して「マテリアライズド・ビュー・ログ」というものを作ってやる必要がある。
たぶん、テーブルに更新があったらマテビューログにその旨を書き込んで、マテビューはそれを見て変更された箇所を知るんだろう。
なにせ、計算し直すタイミングはテーブル更新と同時とは限らないから。
で、早速マテビューログを作ってやる。
SQL> create materialized view log on TBL_YYMM; マテリアライズド・ビュー・ログが作成されました。
よしよし、出来たぞ。と思って再度マテビューを作ろうとすると、
SQL> create materialized view MV_TEST
2 refresh fast
3 as
4 select
5 YYMM_GROUP,
6 SHOP_CODE,
7 sum(AMOUNT) SUM_AMOUNT
8 from TBL_TEST t
9 inner join TBL_YYMM y
10 on y.YYMM_CODE=t.YYMM_CODE
11 group by YYMM_GROUP,SHOP_CODE
12 ;
from TBL_TEST t
*
行8でエラーが発生しました。:
ORA-12032:
"ユーザー"."TBL_YYMM"のマテリアライズド・ビュー・ログからはROWID列を使用できません。
Oracleのテーブルの隠し項目であるROWID。僕は使わなくても構わないんだけど、集計しつつ表の結合をするには必要らしい。
マテビューログを作り直してやる。
(ちなみにマテビューやマテビューログに関しては「create or replace」構文は無いようなので、drop&createで。)
SQL> drop materialized view log on TBL_YYMM;
マテリアライズド・ビュー・ログが削除されました。
SQL> create materialized view log on TBL_YYMM
2 with ROWID;
マテリアライズド・ビュー・ログが作成されました。
再びマテビューを作成すると、また別のエラー。
ORA-32401: "ユーザー"."TBL_YYMM"のマテリアライズド・ビュー・ログには新規の値がありません。
「新規の値」とは、updateやinsertした後の、変更後の値を指しているらしい。デフォルトではマテビューログにそれが含まれないようだ。
マテビューは変更後の値を元に再計算するんだから、当然必要だよな…なんでデフォルトで含むようになってないんだろう…?
SQL> create materialized view log on TBL_YYMM
2 with ROWID
3 including new values;
マテリアライズド・ビュー・ログが作成されました。
でもまたマテビュー作成でエラー。
ORA-12033: "ユーザー"."TBL_YYMM"のマテリアライズド・ビュー・ログからはフィルタ列を使用できません。
「フィルタ列」というのは、マテビューで使う項目のことらしい。
マテビューログには、マテビューで必要なデータ(項目)を全部書き込んでおこうってことかな?
(ただし、プライマリキーは暗黙に含まれる為、フィルタ列には指定しちゃいけないらしい)
SQL> create materialized view log on TBL_YYMM 2 with ROWID,sequence (YYMM_CODE,YYMM_GROUP) 3 including new values; マテリアライズド・ビュー・ログが作成されました。
これでどうやら、TBL_YYMMに対するマテビューログはいいようだ。このテーブルに関するエラーは消えた。
この例で使っているもう一つのテーブルTBL_TESTで「マテビューログがない」というエラーに変わったから(爆)
SQL> create materialized view log on TBL_TEST 2 with ROWID,sequence (YYMM_CODE,SHOP_CODE,AMOUNT) 3 including new values; マテリアライズド・ビュー・ログが作成されました。 SQL> create materialized view MV_TEST 2 refresh fast 3 as 4 select 5 YYMM_GROUP, 6 SHOP_CODE, 7 sum(AMOUNT) SUM_AMOUNT 8 from TBL_TEST t 9 inner join TBL_YYMM y 10 on y.YYMM_CODE=t.YYMM_CODE 11 group by YYMM_GROUP,SHOP_CODE 12 ; マテリアライズド・ビューが作成されました。
ここまでやって、ようやく高速リフレッシュのマテビュー作成でエラーが消えた!
マテビューログ作成におけるwith句には、以下のような指定を組み合わせるらしい。[2007-12-08]
with句 | 概要 | 想像 |
---|---|---|
rowid | 更新されたレコードのROWIDをマテビューログに保持する。 | |
primary key | 更新されたレコードのプライマリキーをマテビューログに保持する。 | プライマリキーは、この指定をしなくても暗黙に含まれるような? |
sequence | 更新された順序をマテビューログに保持する。 | 複数の更新が行われた場合に、最後の更新が分かるようにする為に必要? |
(項目,…) | マテビューログに保持する項目を指定する。 |
マテビューログを変更するには、「alter materialized view log」文を使用する。[2009-01-17]
rem 項目を追加する例 alter materialized view log on テーブル add (追加項目,…);
“テーブルが更新された後、いつマテビューを再計算するか”を指定できる。[2007-12-08]
タイミング | 指定 | 概要 |
---|---|---|
コミット時 | on commit | 更新がコミットされた時点で再計算する。 |
一定間隔 | start with・next | 指定された時刻に再計算する。 |
指定時 | on demand | 手動で指示しない限り、再計算しない。 |
タイミングを指定しない場合はdemandになる。
テーブル更新(コミットされた時)をきっかけにして マテビューを計算し直す指定ができる。
実行タイミングとしては分かり易いが、更新頻度が多いと効率は悪い。
マテビューを作る際に「on commit」を指定する。
SQL> create materialized view MV_TEST
2 refresh fast
3 on commit
4 as
5 select
6 YYMM_GROUP,
7 SHOP_CODE,
8 sum(AMOUNT) SUM_AMOUNT
9 from TBL_TEST t
10 inner join TBL_YYMM y
11 on y.YYMM_CODE=t.YYMM_CODE
12 group by YYMM_GROUP,SHOP_CODE
13 ;
マテリアライズド・ビューが作成されました。
でも、この例だと実際の動作がうまくいかないんだよねー…。
集計関数を使う場合の仕様を確認してなかったのがいけなかったのだが。[2007-12-08]
指定した時刻(時間間隔)でマテビューを計算し直す指定ができる。
「一日一回しか計算し直さなくていい」とか「数分おきに計算したい」とか言うならこの方法がいいでしょう。
マテビューを作る際に「start with」「next」を指定する。
SQL> create materialized view MV_TEST 2 refresh fast 3 start with '2005/2/6' 4 next sysdate + 1 5 as 6 select 7 YYMM_GROUP, 8 SHOP_CODE, 9 sum(AMOUNT) SUM_AMOUNT 10 from TBL_TEST t 11 inner join TBL_YYMM y 12 on y.YYMM_CODE=t.YYMM_CODE 13 group by YYMM_GROUP,SHOP_CODE 14 ; マテリアライズド・ビューが作成されました。
初回の計算日時を「start with」で指定し、次回以降の計算日時を求める為の式を「next」に書く。
この指定を行うと、Oracleのジョブスケジュール機能(?)に登録されるらしい。[2007-12-08]
以下のSQLで実行タイミングを確認することが出来る。
SQL> select last_date,last_sec,next_date,next_sec,interval,what from user_jobs; LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC ---------- ---------------- ---------- ---------------- INTERVAL ----------------------------------------------------------------------------------- WHAT ----------------------------------------------------------------------------------- 2007/12/08 16:14:56 2007/12/08 16:17:56 sysdate + 1/24/60*3 dbms_refresh.refresh('"SCOTT"."MV_TEST"');
↑「start with sysdate next sysdate + 1/24/60*3
」(マテビュー作成時から3分間隔)で指定した例
LAST_DATE/SECが最後に実行した日時、NEXT_DATE/SECが次に実行する日時。(見た感じ、実際の実行は数秒ずつずれていってるっぽい。あまり厳密には使えないのかもね)
WHATがその際に実行するPL/SQL。
と思われる。
なお、マテビューを削除(DROP)すると、このスケジューリング情報も削除(DELETE)される。
それから、dbms_refreshプロシージャのmake()等を使って、 別途リフレッシュのスケジューリングを行うことも出来るらしい。
完全リフレッシュでも高速リフレッシュでも、手動でマテビューを計算し直す指示が出せる。[2007-12-08]
SQL> exec dbms_mview.refresh('MV_TEST')
明示的に'c'を指定すると完全リフレッシュ、'f'だと高速リフレッシュで再計算する。
SQL> exec dbms_mview.refresh('MV_TEST','c')
PL/SQLプロシージャが正常に完了しました。
マテビューが高速リフレッシュで作られていても、'c'を指定すれば完全リフレッシュで再計算(要するに全体が再作成)される。
'f'を指定した場合、マテビューが高速リフレッシュできる状態になっていないと、エラーになる。
あと、dbms_mview.refresh_dependent()で、指定されたテーブルを使っているマテビューを再計算するらしい。
また、dbms_mview.refresh_all_mviews()で、全マテビューを再計算するらしい。
ただし、第1引数にエラー時の戻り値を格納する変数を渡さなきゃいけないので、refresh()ほど簡単には使えない…。
マテビューの状況を取得するプロシージャ(dbms_mview.explain_mview('ビュー名')
)がある。[2007-12-08]
マテビューの作成直後や、マテビューを構成しているテーブルのDML(update/insert/delete)実行後などに状況が変化する。
SQL> exec dbms_mview.explain_mview('mv_test');
BEGIN dbms_mview.explain_mview('mv_test'); END;
*
行1でエラーが発生しました。:
ORA-30377: MV_CAPABILITIES_TABLEが見つかりません。
ORA-00942: 表またはビューが存在しません。
ORA-06512: "SYS.DBMS_XRWMV", 行0
ORA-06512: "SYS.DBMS_SNAPSHOT", 行1378
ORA-06512: 行1
使うには、MV_CAPABILITIES_TABLEというテーブルが必要らしい。
このテーブルは、Oracleをインストールしたディレクトリにあるスクリプトを実行すれば作れる。
(Windowsの場合)コマンドプロンプト:
>cd C:\oracle\ora92\rdbms\admin >sqlplus scott/tiger @utlxmv.sql 〜 表が作成されました。
これで、explain_mview()が使えるようになる。
SQL> delete MV_CAPABILITIES_TABLE;
n行が削除されました。
SQL> exec dbms_mview.explain_mview('mv_test');
PL/SQLプロシージャが正常に完了しました。
SQL> select * from MV_CAPABILITIES_TABLE order by SEQ;
explain_mview()は、MV_CAPABILITIES_TABLEにマテビューの状況を書き込む(insertする)。
したがって、事前に該当マテビューの情報をdeleteしておかないと、実行する度にどんどんレコードが増えていく。
で、参照はMV_CAPABILITIES_TABLEをselectすることによって行う。
CAPABILITY_NAME項目が状況の名称で、POSSIBLE項目が「Y」だと使用可能、「N」だと不可能を表しているっぽい。
で、エラーを表している明細行では、そのエラーが起きている原因がMSGTXT項目に書かれているっぽい。
CAPABILITY_NAME | MSGNO | MSGTXT | エラー内容(想像) |
---|---|---|---|
REFRESH_FAST_AFTER_INSERT (更新後の高速リフレッシュ) |
2162 | ディテール表にはマテリアライズド・ビュー・ログがありません | RELATED_TEXT項目に表示されているテーブル用のマテビューログが無い。 |
REFRESH_FAST_AFTER_ANY_DML (DML実行後の高速リフレッシュ) |
2161 | REFRESH_FAST_AFTER_ONETAB_DMLを使用できない理由を参照してください | 別の明細行(すなわち他のエラー)に、REFRESH_FAST_AFTER_ONETAB_DMLがある。 それが出ているせいで、高速リフレッシュを行うことが出来ない。 |
REFRESH_FAST_AFTER_ONETAB_DML (1つのテーブルのDML実行後?の高速リフレッシュ) |
2143 | COUNT(expr)のないSUM(expr) | SUMを使う場合、マテビューで表示する項目にCOUNTが必要。create materialized view 〜 |
2142 | 選択リストにCOUNT(*)が存在しません。 | マテビューで表示する項目にCOUNT(*)が必要。create materialized view 〜 |
|
2077 | マテリアライズド・ビュー・ログは最新の全体リフレッシュよりも新しいです | RELATED_TEXT項目に表示されているテーブルが更新されているが、まだマテビューに反映されていない。 | |
2164 | マテリアライズド・ビューはBUILD DEFERREDです | 完全リフレッシュを行うまで、マテビューにデータが入っていない(以前の状態のままである)。 |
さて、マテビューを作った直後にマテビューに対してselectしてやると、当然結果が返ってくる。
SQL> select * from MV_TEST;
ところが今回の例では、元テーブルの値を更新して(当然commitもして)再度マテビューをselectしてやっても、結果が変わらない!
完全リフレッシュのマテビューは結果がちゃんと期待通りに変わったんだけど、高速リフレッシュのマテビューは値に変化がない。
強制計算を指示してやると、エラーが出る…。
SQL> execute dbms_mview.refresh('MV_TEST'); BEGIN dbms_mview.refresh('MV_TEST'); END; * 行1でエラーが発生しました。: ORA-12057: マテリアライズド・ビュー"ユーザー"."MV_TEST"はINVALIDなので、完全リフレッシュを行う必要があります。 ORA-06512: "SYS.DBMS_SNAPSHOT", 行794 ORA-06512: "SYS.DBMS_SNAPSHOT", 行851 ORA-06512: "SYS.DBMS_SNAPSHOT", 行832 ORA-06512: 行1
何がINVALIDなんだか分からないけど、とにかく何かがうまくいってないらしい。
完全リフレッシュを実行してやると、
SQL> exec dbms_mview.refresh('MV_TEST','c')
PL/SQLプロシージャが正常に完了しました。
うまくいったようだ。
ちなみに、2つ目の引数に'f'
を指定すると高速リフレッシュね。
根本的な原因は、マテビューの状況を見て調査する。[2007-12-08]
今回のケースでは、集計関数SUMを使っているのにCOUNTやCOUNT(*)を使っていなかったのが敗因。(集計を行うマテビューの仕様)
マテビューのselect項目にcount(AMOUNT)とcount(*)を追加してやったら、高速リフレッシュでもちゃんと反映されるようになった。
create materialized view MV_TEST
refresh fast on commit
as
select
YYMM_GROUP,
SHOP_CODE,
count(*),
count(AMOUNT) CNT_AMOUNT,
sum(AMOUNT) SUM_AMOUNT
from 〜
それにしても、こういう事なら暗黙に入れてくれりゃいーのに。
難しすぎるよ、マテリアライズド・ビュー。(仕様の把握が大変)
素直にトリガー使って別テーブルに計算して入れる方が簡単な気がしちゃう。(でも出来てしまえばトリガーよりシンプルかな。修正も簡単そうだし)
マテビューの内容によるんだろうけど…。
テーブルにuser_tables、ビューにuser_viewsがあるように、マテビューはuser_mviewsを使う。
user_mviewsのqueryという項目にマテビューの定義(本体)が入っている。[2008-02-19]
(user_viewsのtextよりも加工のされ方が激しい)
SQL> create materialized view mv_emp 2 as 3 select * from emp; マテリアライズド・ビューが作成されました。 SQL> set long 9999 SQL> select query from user_mviews 2 where mview_name = 'MV_EMP'; QUERY -------------------------------------------------------------------------------- SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"." DEPTNO" "DEPTNO" FROM "EMP" "EMP"
マテビューを変更するには、「alter
materialized view」文を使う。[2009-01-17]
(マテビューログを変更するのはalter materialized view
log)
ただし、変更できるのは基本的にマテビューの属性(計算方法など)のみ。
表示する項目やデータ取得の為のSQL文は変更できなさそう…。
→OTNのマテリアライズド・ビューの変更
…変更できる操作の一覧
項目に関する変更は出来ないはずなのだが、以下のように、半端な操作は可能。[2009-01-17]
SQL> alter materialized view MV_TEST 2 add (zzz char(4)); マテリアライズド・ビューが変更されました。 SQL> desc MV_TSET 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- YYMM_GROUP CHAR(6) SHOP_CODE CHAR(4) CT NUMBER CTA NUMBER SUM_AMOUNT NUMBER ZZZ VARCHAR2(4) ←CHAR(4)で作ったはずなのに、なぜかVARCHAR2だが…
ZZZ項目をマテビューログに追加してリフレッシュし直しても、selectしたらnullしか表示されない。
なぜかというと、内部のSQLは変わっていないから。SQLを変える方法は無いようなので、項目だけ追加できても無意味だ…。
なお、追加した項目の削除は以下の様に「alter table」文を使用する。
SQL> alter materialized view MV_TEST 2 drop (ZZZ); drop (ZZZ) * 行2でエラーが発生しました。: ORA-14051: ALTER MATERIALIZED VIEWのオプションが無効です。 SQL> alter table MV_TEST 2 drop (ZZZ); 表が変更されました。