敬遠されがちなマテリアライズド・ビュー
今回は「マテリアライズド・ビュー」（以下、マテビュー）について記載していきたいと思います。
マテビューはテーブルのような実体を持ったビューであります。
実際にマテビューを作成した後、ディクショナリビューのALL_OBJECTSを確認すると「MATERIALIZED VIEW」「TABLE」２つの存在が確認できます。
通常のビューは問合せ毎に結合、演算、集計など色々行い結果を返しますが、
マテビューは結果を実体で保持していますので、問合せが来たらそのまま結果を返します。
この仕組みにより、問合せのスピードが早いという特性があります。
では実際どの程度早くなるかを今回は検証していきたいと思います。
検証内容
ビューとマテビューの問合せ速度の差を調べます。
検証詳細
同じ構成のビューとマテビューに対しSELECT文を発行し、その実行時間を取得します。
試行回数は10回行い、その平均時間を結果とします。
パターンは下記の２パターン行います。
・結合のみ行ったケース
・結合と集計をしたケース
検証環境
今回の検証環境は以下の通りです。
マシン情報
検証準備
１、テーブルを５つ作成し、各テーブルにデータを10万件ずつ投入します。
２、５つのテーブル結合したビューを作成します
３、同じ構成のマテビューを用意します。
４、両者の統計情報を最新の状態にします。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
--【用意したテーブル】各テーブルにデータを100万件用意
CREATE TABLE "TBL_01"
(
"F_ID" NUMBER NOT NULL,
"F_VALUE" NUMBER,
CONSTRAINT "PK_TBL_01" PRIMARY KEY ("F_ID") USING INDEX
)
/
CREATE TABLE "TBL_02"
(
"F_ID" NUMBER NOT NULL,
"F_VALUE" NUMBER,
CONSTRAINT "PK_TBL_02" PRIMARY KEY ("F_ID") USING INDEX
)
/
CREATE TABLE "TBL_03"
(
"F_ID" NUMBER NOT NULL,
"F_VALUE" NUMBER,
CONSTRAINT "PK_TBL_03" PRIMARY KEY ("F_ID") USING INDEX
)
/
CREATE TABLE "TBL_04"
(
"F_ID" NUMBER NOT NULL,
"F_VALUE" NUMBER,
CONSTRAINT "PK_TBL_04" PRIMARY KEY ("F_ID") USING INDEX
)
/
CREATE TABLE "TBL_05"
(
"F_ID" NUMBER NOT NULL,
"F_VALUE" NUMBER,
CONSTRAINT "PK_TBL_05" PRIMARY KEY ("F_ID") USING INDEX
)
/
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
--【用意したビュー】（結合のみ行ったケース）
CREATE OR REPLACE VIEW "VIW_JOIN"
( "F_ID"
, "F_VALUE01"
, "F_VALUE02"
, "F_VALUE03"
, "F_VALUE04"
, "F_VALUE05"
)
AS
SELECT
TBL_01.F_ID
, TBL_01.F_VALUE AS "F_VALUE01"
, TBL_02.F_VALUE AS "F_VALUE02"
, TBL_03.F_VALUE AS "F_VALUE03"
, TBL_04.F_VALUE AS "F_VALUE04"
, TBL_05.F_VALUE AS "F_VALUE05"
FROM
TBL_01
LEFT JOIN TBL_02 ON TBL_01.F_ID = TBL_02.F_ID
LEFT JOIN TBL_03 ON TBL_01.F_ID = TBL_03.F_ID
LEFT JOIN TBL_04 ON TBL_01.F_ID = TBL_04.F_ID
LEFT JOIN TBL_05 ON TBL_01.F_ID = TBL_05.F_ID
/
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
--【用意したマテビュー】（結合のみ行ったケース）
CREATE MATERIALIZED VIEW "MTV_JOIN"
REFRESH COMPLETE ON DEMAND
AS
SELECT
TBL_01.F_ID
, TBL_01.F_VALUE AS "F_VALUE01"
, TBL_02.F_VALUE AS "F_VALUE02"
, TBL_03.F_VALUE AS "F_VALUE03"
, TBL_04.F_VALUE AS "F_VALUE04"
, TBL_05.F_VALUE AS "F_VALUE05"
FROM
TBL_01
LEFT JOIN TBL_02 ON TBL_01.F_ID = TBL_02.F_ID
LEFT JOIN TBL_03 ON TBL_01.F_ID = TBL_03.F_ID
LEFT JOIN TBL_04 ON TBL_01.F_ID = TBL_04.F_ID
LEFT JOIN TBL_05 ON TBL_01.F_ID = TBL_05.F_ID
/
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
--【用意したビュー】（結合と集計をしたケース）
CREATE OR REPLACE VIEW "VIW_SUM"
( "F_ID"
, "F_VALUE01"
, "F_VALUE02"
, "F_VALUE03"
, "F_VALUE04"
, "F_VALUE05"
, "F_SUM_VALUE")
AS
SELECT
TBL_01.F_ID
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 1 AS "F_SUM_VALUE01"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 2 AS "F_SUM_VALUE02"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 3 AS "F_SUM_VALUE03"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 4 AS "F_SUM_VALUE04"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 5 AS "F_SUM_VALUE05"
FROM
TBL_01
LEFT JOIN TBL_02 ON TBL_01.F_ID = TBL_02.F_ID
LEFT JOIN TBL_03 ON TBL_01.F_ID = TBL_03.F_ID
LEFT JOIN TBL_04 ON TBL_01.F_ID = TBL_04.F_ID
LEFT JOIN TBL_05 ON TBL_01.F_ID = TBL_05.F_ID
/
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
--【用意したマテビュー】（結合と集計をしたケース）
CREATE MATERIALIZED VIEW "MTV_SUM"
REFRESH COMPLETE ON DEMAND
AS
SELECT
TBL_01.F_ID
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 1 AS "F_SUM_VALUE01"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 2 AS "F_SUM_VALUE02"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 3 AS "F_SUM_VALUE03"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 4 AS "F_SUM_VALUE04"
, TBL_01.F_VALUE + TBL_02.F_VALUE + TBL_03.F_VALUE
+ TBL_04.F_VALUE + TBL_05.F_VALUE * 5 AS "F_SUM_VALUE05"
FROM
TBL_01
LEFT JOIN TBL_02 ON TBL_01.F_ID = TBL_02.F_ID
LEFT JOIN TBL_03 ON TBL_01.F_ID = TBL_03.F_ID
LEFT JOIN TBL_04 ON TBL_01.F_ID = TBL_04.F_ID
LEFT JOIN TBL_05 ON TBL_01.F_ID = TBL_05.F_ID
/
|
実験は単純にSELECT文（SELECT * FROM ビュー）で処理時間を計測します。
また、条件を整えるために１回測定ごとにキャッシュのクリアを行います。
|
1
2
3
4
|
ALTER SYSTEM FLUSH SHARED_POOL
/
ALTER SYSTEM FLUSH BUFFER_CACHE
/
|
結果は、以下のとおりとなりました。
検証結果
※パターンの内容は以下のとおりです。
パターン１：単純な結合
パターン２：単純な結合＋計算
処理時間(単位：秒)
| パターン1 | パターン2 | |||
|---|---|---|---|---|
| ビュー | マテビュー | ビュー | マテビュー | |
| 1回目 | 6.166 | 1.631 | 6.515 | 1.380 |
| 2回目 | 9.065 | 1.534 | 6.566 | 1.378 |
| 3回目 | 6.012 | 1.298 | 6.580 | 1.408 |
| 4回目 | 6.351 | 1.424 | 6.570 | 1.450 |
| 5回目 | 6.361 | 1.389 | 7.631 | 1.372 |
| 6回目 | 5.626 | 1.473 | 8.087 | 1.360 |
| 7回目 | 5.957 | 1.425 | 5.881 | 1.394 |
| 8回目 | 6.263 | 1.359 | 6.568 | 1.483 |
| 9回目 | 6.110 | 1.369 | 5.976 | 1.469 |
| 10回目 | 6.096 | 1.407 | 6.235 | 1.603 |
| 平均 | 6.401 | 1.431 | 6.661 | 1.430 |
マテビューがビューの４倍程早い結果になりました。
集計をしたパターンはもう少し差が出ると思ったのですが、そうでもありませんでした。
今回の場合は結合の処理がビューにとって重荷になったようです。 [RELATED_POSTS]
結論
マテビューはビューより早いです。
結果を実テーブルに入れているため、
おそらくどんな条件でもSELECT問合せに関しては勝利するでしょう。
しかしながら、実際の開発でマテビューはあまり使われません。
何故でしょうか。それは・・・。（それはまた、次回以降の検証で。）
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b2.png)
