More than 5 years have passed since last update.

Viewのマージ

oracle

Last updated at 2016-09-11Posted at 2016-08-28

Viewのマージとは

あるViewと他のテーブルと結合するようなSQLを実行する場合、View自体のSQLが実行されてから他のテーブルと結合するのではなく、オプティマイザによって効率がよいと判断された場合には、ViewのSQLがばらされて、他のテーブルとの結合が先に実行される場合があります。これをViewのマージと呼ぶそうです。似たようなものに、述部のプッシュがありますが、これについては別の記事に書こうと思います。

このOracle社のページに非常に有益な情報が書かれているのですが、ちょっとわかりづらいので、自分の理解を深める意味も含めて、自分の言葉で解説してみます。

例

SQL

select * from a_view v, a_table t
where v.val = t.val

a_view

create or replace view a_view as
select to_number(v.val) val from value_table v, type_table t
where v.id = t.id and t.type = 'NUMBER'

value_table

create table value_table (
  id number primary key,
  val varchar2(256)
)

type_table

create table type_table (
  id number primary key,
  type varchar2(10)
)

データ

        ID VAL                ID TYPE
---------- ---------- ---------- ----------
     10000 ABC             10000 TEXT
     10001 100             10001 NUMBER
     ...                   ...

value_tableのval列には、数値もしくは文字列のデータの両方が入るため、データの種別をtype_tableのtype列で管理しています。a_viewでは数値のデータのみを抜きだしています。その値とa_tableのval列（こちらは最初から数値のみ）で結合しようとしています。

普通に考えると、v.valには数値しかないはずなので、問題なく結合が実行できると思うでしょう。しかし実際にはうまくいかない場合があるのです。

うまくいくケース

実行計画

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |    67 |  1608 |    10  (20)| 00:00:01 |
|*  1 |  HASH JOIN                    |             |    67 |  1608 |    10  (20)| 00:00:01 |
|   2 |   MERGE JOIN                  |             |   101 |  2020 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| VALUE_TABLE |   201 |  1809 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | SYS_C009896 |   201 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |   101 |  1111 |     4  (25)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL         | TYPE_TABLE  |   101 |  1111 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL           | A_TABLE     |     2 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."VAL"=TO_NUMBER("V"."VAL"))
   5 - access("V"."ID"="T"."ID")
       filter("V"."ID"="T"."ID")
   6 - filter("T"."TYPE"='NUMBER')

この実行計画では、value_tableと、type列が'NUMBER'である行のみを抜き出したtype_tableを結合した上で、a_tableと結合する際にval列に対してto_numberを実行しています。ViewのSQLの中にあったto_numberが外側に出てきています。

このようにViewのSQLがそのまま実行されるのではなく、ばらされて最適な形で実行されるのです。このケースでは、テーブルの結合順序までは変わっていないので、問題なく実行されます。

エラーになるケース

実行計画

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   593K|    14M|       |  2797   (1)| 00:00:34 |
|*  1 |  HASH JOIN          |             |   593K|    14M|    14M|  2797   (1)| 00:00:34 |
|*  2 |   HASH JOIN         |             |   593K|  7532K|       |   489   (2)| 00:00:06 |
|   3 |    TABLE ACCESS FULL| A_TABLE     |     2 |     8 |       |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| VALUE_TABLE |   890K|  7822K|       |   482   (2)| 00:00:06 |
|*  5 |   TABLE ACCESS FULL | TYPE_TABLE  |   889K|    10M|       |   587   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("V"."ID"="T"."ID")
   2 - access("T"."VAL"=TO_NUMBER(TO_CHAR(TO_NUMBER("V"."VAL"))))
   5 - filter("T"."TYPE"='NUMBER')

もしvalue_tableの行数がとても多く、a_tableの行数がとても少ない場合は、先にa_tableと結合してしまった方が効率がいいということになります。Oracleのオプティマイザは賢いので実際にそのような動きをします。

Viewのマージによって、通常はSQLのパフォーマンスが上がってめでたしめでたしなのですが、このケースの場合は、value_tableの数値のデータを抜き出す前に、to_numberをval列に対して実行することになり、文字列のデータの行を処理する際にエラーになってしまいます。

ヒントの使用による解決

では、エラーにならないようにするためにはどうしたらいいのでしょうか？幸いにもViewのマージをしないようにオプティマイザに指示できるヒント句があります。

SQL

select /*+ NO_MERGE(v) */ * from a_view v, a_table t
where v.val = t.val

v(a_view)のマージはしないでね、ということです。これで先にViewのSQLが確実に実行されるようになり、エラーは発生しなくなります。

実行計画

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    17 |       |  2981   (1)| 00:00:36 |
|*  1 |  HASH JOIN           |             |     1 |    17 |       |  2981   (1)| 00:00:36 |
|   2 |   TABLE ACCESS FULL  | A_TABLE     |     2 |     8 |       |     3   (0)| 00:00:01 |
|   3 |   VIEW               | A_VIEW      |   889K|    11M|       |  2975   (1)| 00:00:36 |
|*  4 |    HASH JOIN         |             |   889K|    17M|    17M|  2975   (1)| 00:00:36 |
|   5 |     TABLE ACCESS FULL| VALUE_TABLE |   890K|  7822K|       |   482   (2)| 00:00:06 |
|*  6 |     TABLE ACCESS FULL| TYPE_TABLE  |   889K|    10M|       |   587   (2)| 00:00:08 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("V"."VAL"="T"."VAL")
   4 - access("V"."ID"="T"."ID")
   6 - filter("T"."TYPE"='NUMBER')

Viewを作ってから、a_tableと結合しています。普通に考えると必ずこのように実行されると思ってしますのですが、違うんですね。Viewを結合するSQLを書く際にはちょっと注意が必要です。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up