Oracle JOINで結合してるとクエリーリライトされない

2022-12-21

マテリアライズドビューの勉強をしています。

ちょっとハマったことがありましたので
メモってみたいと思います。

結論を言ってしまうと、
マテビューのクエリーリライトを使いたいときは
JOINで結合しない方が良さそう！
というお話です。

なお、試してるOracleのバージョンは19Cです。

サンプルデータ

こんな感じのデータで試しました。

こちら【fact_table】です。

itemcustomersales_dateamnt
コシヒカリ鈴木2022/04/01500
あきたこまち伊藤2022/04/10250
水田口2022/05/08300
お茶相田2022/04/30300
たくあん鈴木2022/04/03150

で、こちらが【dim_item】です。
itemの分類を定義するイメージです。

item_groupitem
お米コシヒカリ
お米あきたこまち
飲み物水
飲み物お茶
漬物たくあん

JOINで結合する例

JOINを使って結合するの…
割と普通ですよね？

CREATE MATERIALIZED VIEW mv_item_group_sales
  ENABLE QUERY REWRITE
  AS
  SELECT
    dim.item_group   item_group
   ,sum(f_tbl.amnt)  amnt
  FROM
    fact_table f_tbl
  LEFT OUTER JOIN
    dim_item dim
  ON
    dim.item = f_tbl.item
  GROUP BY
    dim.item_group
;

「mv_item_group_sales」という名前のマテビューを作りました。

９行目の通り、
「LEFT OUTER JOIN」つまり外部結合しています。

作成したマテビューと全く同じクエリならリライトする

以下のように実行計画を取ってみます。。。

EXPLAIN PLAN FOR
SELECT
  dim.item_group   item_group
 ,sum(f_tbl.amnt)  amnt
FROM
  fact_table f_tbl
LEFT OUTER JOIN
  dim_item dim
ON
  dim.item = f_tbl.item
GROUP BY
  dim.item_group
;

CREATE VIEW PLAN AS 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'SERIAL'));

SELECT * FROM PLAN;

2〜12行目は、
作成したマテビューと全く同じクエリです。

これで実行計画を見ると…

マテビュー「mv_item_group_sales」を
使ってくれそうですね！

めでたしめでたし…
とはならないんですよ。。。

何か条件を付けてみるとリライトされない

WHERE句の中に
何か条件を追加してみると…

EXPLAIN PLAN FOR
SELECT
  dim.item_group   item_group
 ,sum(f_tbl.amnt)  amnt
FROM
  fact_table f_tbl
LEFT OUTER JOIN
  dim_item dim
ON
  dim.item = f_tbl.item
WHERE
  dim.item_group = 'お米'
GROUP BY
  dim.item_group
;

drop view plan
cascade constraints
;

CREATE VIEW PLAN AS 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'SERIAL'));

SELECT * FROM PLAN;

11、12行目を増やしただけです。
「お米」に絞っただけ…で、結果は以下になります。

マテビュー使っていません。
めっちゃテーブルアクセスしてる。。。

JOINを使用しないで結合する例

JOINを使用しないで
マテビューを作成してみます。

CREATE MATERIALIZED VIEW mv_item_group_sales
  ENABLE QUERY REWRITE
  AS
  SELECT
    dim.item_group   item_group
   ,sum(f_tbl.amnt)  amnt
  FROM
    fact_table f_tbl
   ,dim_item dim
  WHERE
    f_tbl.item = dim.item(+)
  GROUP BY
    dim.item_group
;

マテビュー「mv_item_group_sales」を作成しました。

作成したマテビューと全く同じクエリ

JOINの例と同じように、
まずは全く同じクエリを投げて
実行計画がどうなっているか？確認してみます。

EXPLAIN PLAN FOR
SELECT
  dim.item_group   item_group
 ,sum(f_tbl.amnt)  amnt
FROM
  fact_table f_tbl
 ,dim_item dim
WHERE
  f_tbl.item = dim.item(+)
GROUP BY
  dim.item_group
;

CREATE VIEW PLAN AS 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'SERIAL'));

SELECT * FROM PLAN;

結果は以下のようになります。

マテビュー「mv_item_group_sales」を見てくれていますね。

何か条件を付けてみる

WHERE句で「お米」だけに絞ったクエリを投げてみると
どうなるでしょうか…

EXPLAIN PLAN FOR
SELECT
  dim.item_group   item_group
 ,sum(f_tbl.amnt)  amnt
FROM
  fact_table f_tbl
 ,dim_item dim
WHERE
  f_tbl.item = dim.item(+)
AND
  dim.item_group = 'お米'
GROUP BY
  dim.item_group
;

CREATE VIEW PLAN AS 
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'SERIAL'));

SELECT * FROM PLAN;

10、11行目が増えただけです。

JOINを使った結合の場合は
マテビューを使ってくれませんでしたね。

結果は以下となりました。

マテビュー「mv_item_group_sales」使ってくれてる！
ちゃんと「お米」で絞ってくれてそう！

以上ですｗ

現象だけやっていきました。

普通、マテビュー使う時って
テーブルの結合すると思うんですよ。

で、マテビューに用意したクエリを
そっくりそのまま使うことって、
そんなにないと思うんですよ。。。

何だかな〜。。。

オラクルさん家のオプティマイザさん、
マテビューのクエリーリライトのところだけ古いのかしら…

ということで、マテビュー使うときは
現状、JOINは使わない方が良いのかもですね。

OracleDB

Posted by kiri