Oracle12c ヒント句による実行計画の制御[ORDERED, LEADING, USE_NL, INDEX, OPT_PARAM]

概要

SQLが望ましくない実行計画となっている場合に、
ヒント句により結合順などを制御する例をあげます。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

結合テスト用オブジェクト、データ用意

^ テスト用テーブルを３つ作成

-- 株式銘柄テーブル
create table tab_stock
(  
    stock_cd      number(10)
  , stock_name    varchar2(256)
  , marcket_cd    number(10)
  , industry_cd   number(10)
  , stock_unit    varchar2(48)
  , nikkei225     char(1)
)

-- PK
create unique index idxpk_tab_stock on tab_stock( stock_cd );
alter table tab_stock add constraint idxpk_tab_stock primary key( stock_cd);

-- stock_name検索用
create index idx01_tab_stock on tab_stock( stock_name );

-- 市場名マスタテーブル
create table tab_marcket_master
(
    marcket_cd     number(2)
  , marcket_name   varchar2(256)
)

-- PK
create unique index idxpk_marcket_master on tab_marcket_master( marcket_cd );
alter table tab_marcket_master add constraint idxpk_marcket_master primary key( marcket_cd );

-- 業種名マスタテーブル
create table tab_industry_master
(
    industry_cd     number(2)
  , industry_name   varchar2(256)
)

-- PK
create unique index idxpk_industry_master on tab_industry_master( industry_cd );
alter table tab_industry_master add constraint idxpk_industry_master primary key( industry_cd );

^ 株式上場企業リストを適当なところから持ってきて入れます。

-- 株式銘柄テーブル
SQL> select count(*) from tab_stock;

  COUNT(*)
----------
      3750

1 row selected.

Elapsed: 00:00:00.01
SQL>


  1  select *
  2    from (select s.stock_cd
  3               , s.stock_name
  4               , mm.marcket_name
  5               , im.industry_name
  6               , s.stock_unit
  7               , s.nikkei225
  8               , row_number() over (partition by mm.marcket_cd order by s.stock_cd desc) rn
  9            from tab_stock s
 10           inner join tab_marcket_master mm
 11              on mm.marcket_cd = s.marcket_cd
 12           inner join tab_industry_master im
 13              on im.industry_cd = s.industry_cd
 14         )
 15   where rn = 1
 16*    and rownum <= 10
SQL> /

  STOCK_CD STOCK_NAME                               MARCKET_NAME         INDUSTRY_NAME        STOCK_UNIT       NIKKEI225          RN
---------- ---------------------------------------- -------------------- -------------------- ---------------- ---------- ----------
      9997 (株)ベルーナ                             東証1部              小売業               100                                  1
      9996 (株)サトー商会                           東証JQS              卸売業               100                                  1
      9980 マルコ(株)                               東証2部              小売業               100                                  1
      9519 (株)レノバ                               マザーズ             電気・ガス業         100                                  1
      9399 新華ホールディングス・リミテッド         東証                 情報・通信           単元制度なし                         1
      9924 (株)ドミー                               名証2部              小売業               1000                                 1
      9385 (株)ショーエイコーポレーション           東証JQG              化学                 100                                  1
      9942 (株)ジョイフル                           福証                 小売業               100                                  1
      6025 日本ＰＣサービス(株)                     名古屋セ             サービス業           100                                  1
      9085 北海道中央バス(株)                       札証                 陸運業               100                                  1

10 rows selected.

Elapsed: 00:00:00.02
SQL>

SQL> select * from tab_marcket_master;

MARCKET_CD MARCKET_NAME
---------- ------------
         1 東証1部
         2 東証JQS
         3 東証2部
         4 マザーズ
         5 東証
         6 名証2部
         7 東証JQG
         8 福証
         9 名古屋セ
        10 札証
        11 noname
        12 札幌ア
        13 福岡Q
        14 名証1部
        15 東証外国

15 rows selected.

Elapsed: 00:00:00.01
SQL>

SQL> select * from tab_industry_master;

INDUSTRY_CD INDUSTRY_NAME
----------- ------------------------------
          1 情報・通信
          2 サービス業
          3 小売業
          4 卸売業
          5 電気機器
          6 機械
          7 化学
          8 建設業
          9 食料品
         10 不動産業
         11 その他製品
         12 輸送用機器
         13 銀行業
         14 金属製品
         15 REIT銘柄一覧
         16 医薬品
         17 陸運業
         18 ガラス・土石製品
         19 繊維製品
         20 精密機器
         21 鉄鋼
         22 証券業
         23 倉庫・運輸関連業
         24 その他金融業
         25 非鉄金属
         26 パルプ・紙
         27 電気・ガス業
         28 ゴム製品
         29 海運業
         30 保険業
         31 石油・石炭製品
         32 水産・農林業
         33 鉱業
         34 空運業

34 rows selected.

Elapsed: 00:00:00.01
SQL>

市場名と業種名はマスタ化して、メインテーブルには各コードを保持します。

実行計画の制御

stoke_nameが「東京」で始まる企業の、市場/業種を集計するSQLを例とします。

---

^ ヒント句なしSQLと実行計画

select mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 556986960

-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                      |    16 |   992 |    16  (13)| 00:00:01 |
|   1 |  SORT GROUP BY                 |                      |    16 |   992 |    16  (13)| 00:00:01 |
|*  2 |   HASH JOIN                    |                      |    16 |   992 |    15   (7)| 00:00:01 |
|   3 |    MERGE JOIN                  |                      |    16 |   736 |    12   (9)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TAB_MARCKET_MASTER   |    15 |   195 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | IDXPK_MARCKET_MASTER |    15 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |                      |    16 |   528 |    10  (10)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL         | TAB_STOCK            |    16 |   528 |     9   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | TAB_INDUSTRY_MASTER  |    34 |   544 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   2 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")
   6 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
       filter("MM"."MARCKET_CD"="S"."MARCKET_CD")
   7 - filter("S"."STOCK_NAME" LIKE '東京%')

23 rows selected.

全部をメモリ上に読み込んで、HASH JOIN、MERGE JOINでガシャッという感じですね。
まあ、テーブル件数も少ないので、単体ではこの方が速いのは事実です。
しかし本番運用では多数のSQLが流れるので、こういった実行計画は
好ましくないことがほとんどです。という理屈をつけて、実行計画を制御します。

---

^ orderedヒント句SQLと実行計画

select /*+ ordered */
       mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2765255774

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |    16 |   992 |    16   (7)| 00:00:01 |
|   1 |  SORT GROUP BY       |                     |    16 |   992 |    16   (7)| 00:00:01 |
|*  2 |   HASH JOIN          |                     |    16 |   992 |    15   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |                     |    16 |   736 |    12   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TAB_STOCK           |    16 |   528 |     9   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TAB_MARCKET_MASTER  |    15 |   195 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | TAB_INDUSTRY_MASTER |    34 |   544 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")
   3 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
   4 - filter("S"."STOCK_NAME" LIKE '東京%')

20 rows selected.

orderedヒント句は、SQLのテーブル記述順でどうですか、というだけなので、
結合順の選択肢が１つしかなく、その順序では実行計画が成り立たないと判断されると、
オプティマイザはヒント句を無視するので、効かないこともしばしば。
ここでは意図あってテーブルを結合順に並べているので、ヒント句は効きました。

---

^ leadingヒント句SQLと実行計画

select /*+ leading(s mm im) */
       mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2765255774

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |    16 |   992 |    16   (7)| 00:00:01 |
|   1 |  SORT GROUP BY       |                     |    16 |   992 |    16   (7)| 00:00:01 |
|*  2 |   HASH JOIN          |                     |    16 |   992 |    15   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |                     |    16 |   736 |    12   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TAB_STOCK           |    16 |   528 |     9   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TAB_MARCKET_MASTER  |    15 |   195 |     3   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | TAB_INDUSTRY_MASTER |    34 |   544 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")
   3 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
   4 - filter("S"."STOCK_NAME" LIKE '東京%')

20 rows selected.

今回の場合、orderedと同じ結合順をleadingに変えているだけなので、
意味合いは全く同じです。当然、実行計画も同じ。
orderedでもleadingでも、結合順はヒント句が効きましたが、
オプティマイザはやはりHASH JOINしたいようです。（間違いではありません）

---

^ use_nlヒント句SQLと実行計画

select /*+ leading(s mm im)
           use_nl(mm im) */
       mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3325498095

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |    16 |   992 |    42   (3)| 00:00:01 |
|   1 |  SORT GROUP BY                  |                       |    16 |   992 |    42   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                       |    16 |   992 |    41   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                       |    16 |   992 |    41   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                       |    16 |   736 |    25   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL          | TAB_STOCK             |    16 |   528 |     9   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| TAB_MARCKET_MASTER    |     1 |    13 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | IDXPK_MARCKET_MASTER  |     1 |       |     0   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN           | IDXPK_INDUSTRY_MASTER |     1 |       |     0   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | TAB_INDUSTRY_MASTER   |     1 |    16 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   5 - filter("S"."STOCK_NAME" LIKE '東京%')
   7 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
   8 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")

23 rows selected.

しかし、本番システム上で1000多重で実行することを考えて、use_nlヒント句により、
結合方法をNestedLoopに変えて見ました。
まとめてメモリ上に読み込まずに、ループしながら必要な件数ずつ読み込むことで、
メモリリソースの使用を抑える意図です。
あとは、せっかく用意したstock_name用のインデックス、idx01_tab_stockを使って欲しいところ。
東京%の条件で、24件まで絞り込めるので。※この想定はオプティマイザには難しい部分

SQL> select count(*) from tab_stock where stock_name like '東京%';

  COUNT(*)
----------
        24

---

^ indexヒント句SQLと実行計画

select /*+ leading(s mm im)
           use_nl(mm im)
           index(s idx01_tab_stock) */
       mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3716217921

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                       |    16 |   992 |    51   (2)| 00:00:01 |
|   1 |  SORT GROUP BY                          |                       |    16 |   992 |    51   (2)| 00:00:01 |
|   2 |   NESTED LOOPS                          |                       |    16 |   992 |    50   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                       |    16 |   992 |    50   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                       |    16 |   736 |    34   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB_STOCK             |    16 |   528 |    18   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | IDX01_TAB_STOCK       |    16 |       |     2   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID        | TAB_MARCKET_MASTER    |     1 |    13 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN                 | IDXPK_MARCKET_MASTER  |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN                   | IDXPK_INDUSTRY_MASTER |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID          | TAB_INDUSTRY_MASTER   |     1 |    16 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   6 - access("S"."STOCK_NAME" LIKE '東京%')
       filter("S"."STOCK_NAME" LIKE '東京%')
   8 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
   9 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")

25 rows selected.

indexヒント句を入れると、これも何とも素直に効いてくれました。

access("S"."STOCK_NAME" LIKE '東京%')

今回のヒント句を入れたものが、必ずしも最適解というわけではありませんが、
オンラインで多数実行される場合に、メモリリソースを抑えるようなケースでは、
このような実行計画が無難なものとなりますね。

---

もう１つはおまけで。

^ opt_paramヒント句SQLと実行計画

select /*+ opt_param('optimizer_index_cost_adj', 1)  */
       mm.marcket_name
     , im.industry_name
     , count(*)
  from tab_stock s
 inner join tab_marcket_master mm
    on mm.marcket_cd = s.marcket_cd
 inner join tab_industry_master im
    on im.industry_cd = s.industry_cd
 where s.stock_name like '東京%'
 group by
       mm.marcket_name
     , im.industry_name
 order by
       1, 2
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3716217921

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                       |    16 |   992 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                          |                       |    16 |   992 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                          |                       |    16 |   992 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                       |    16 |   992 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                       |    16 |   736 |     2   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB_STOCK             |    16 |   528 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | IDX01_TAB_STOCK       |    16 |       |     1   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID        | TAB_MARCKET_MASTER    |     1 |    13 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN                 | IDXPK_MARCKET_MASTER  |     1 |       |     1   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN                   | IDXPK_INDUSTRY_MASTER |     1 |       |     1   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID          | TAB_INDUSTRY_MASTER   |     1 |    16 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

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

   6 - access("S"."STOCK_NAME" LIKE '東京%')
       filter("S"."STOCK_NAME" LIKE '東京%')
   8 - access("MM"."MARCKET_CD"="S"."MARCKET_CD")
   9 - access("IM"."INDUSTRY_CD"="S"."INDUSTRY_CD")

25 rows selected.

opt_paramヒント句で、このSQL実行時のoptimizer_index_cost_adjの値を1（デフォルトは100）
に変更してみると、あらら。
leadingもuse_nlも無しで、この実行計画になりました。