Go to Qiita Advent Calendar 2024 Top
LoginSignup
8
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@notsumitsuru(motsuni2)

ヒント句の書き方

Posted at

ヒント句の書き方

実務でヒントを書く機会があったので調べたことをメモしておきます。
ヒント句なしでは、NESTED LOOP結合が採用されるSQLをヒント句を書くことでHASH結合されるようにしてみましょう。

ヒント句なし
explain plan
  set statement_id = 'no-hint'
  for select
          e.empid
        , e.empname
        , d.deptid
        , d.deptname
      from
          emp e
            inner join dept d
              on d.deptid = e.deptid
      where
          e.empname = 'aaaa10'
;

実行計画はNESTED LOOP結合になっています。

実行結果
SQL> r
  1* select * from table(dbms_xplan.display(format=>'ADVANCED', statement_id=>'no-hint'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1238628783

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |   210 |    71   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |     3 |   210 |    71   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP         |     3 |   123 |    68   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C007535 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    29 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$58A6D7F6" "D"@"SEL$1")
      USE_NL(@"SEL$58A6D7F6" "D"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$58A6D7F6" "D"@"SEL$1" ("DEPT"."DEPTID"))
      FULL(@"SEL$58A6D7F6" "E"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

それでは、普通にヒント句を書いてみましょう。HASH結合のヒント句はuse_hash(表名)です。このとき気を付けることはクエリ内で表に別名を指定している場合は別名を書く必要があることです。

普通の書き方
explain plan
  set statement_id = 'normal-hint'
  for select
          /*+ use_hash(e d) */ -- 指定した別名でテーブルを指定
          e.empid
        , e.empname
        , d.deptid
        , d.deptname
      from
          emp e
            inner join dept d
              on d.deptid = e.deptid
      where
          e.empname = 'aaaa10'
;

実行計画がHASH結合に変わりました。

実行結果
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED', statement_id=>'normal-hint'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    53 |   101   (1)| 00:00:02 |
|*  1 |  HASH JOIN         |      |     1 |    53 |   101   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    32 |    68   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT | 30000 |   615K|    32   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$58A6D7F6" "D"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "D"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "D"@"SEL$1")
      FULL(@"SEL$58A6D7F6" "E"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

別名ではなく、表名をそのまま記載してみます。

だめな書き方
explain plan
  set statement_id = 'ng-hint'
  for select
          /*+ use_hash(emp dept) */ -- テーブル名を直接指定（ただし、表に別名をつけていなければOK）
          e.empid
        , e.empname
        , d.deptid
        , d.deptname
      from
          emp e
            inner join dept d
              on d.deptid = e.deptid
      where
          e.empname = 'aaaa10'
;

実行計画にHASH結合が採用されず、NESTED LOOP結合になりました。

実行結果
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED', statement_id=>'ng-hint'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 1238628783

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    53 |    69   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |     1 |    53 |    69   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP         |     1 |    32 |    68   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C007535 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    21 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$58A6D7F6" "D"@"SEL$1")
      USE_NL(@"SEL$58A6D7F6" "D"@"SEL$1")
      LEADING(@"SEL$58A6D7F6" "E"@"SEL$1" "D"@"SEL$1")
      INDEX(@"SEL$58A6D7F6" "D"@"SEL$1" ("DEPT"."DEPTID"))
      FULL(@"SEL$58A6D7F6" "E"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      MERGE(@"SEL$1")
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

最後にview内のテーブルに対し、ヒント句を指定したい場合はどうすればよいでしょうか。viewのDDL内にヒント句を書くこともできますが、これは推奨されていません。viewを使用する側で最適な実行計画が採用されることを妨げるからのようです。そこで、グローバルヒントを使用します。
グローバルヒントは(@queryblock 表1, 表2,...)の形式で書きます。

グローバルヒント
create or replace view v as
  select
      e.empid
    , e.empname
    , d.deptid
    , d.deptname
  from
      emp e
        inner join dept d
          on d.deptid = e.deptid
;
explain plan
  set statement_id = 'grobal-hint'
  for select
          /*+ use_hash(@sel$2 e d) */ -- grobal hint
          empid
        , empname
        , deptid
        , deptname
      from
          v
      where
          empname = 'aaaa10'
;

view内のテーブルに対してもヒント句を指定してHASH結合が採用されました。

実行結果
SQL> select * from table(dbms_xplan.display(format=>'ADVANCED', statement_id=>'grobal-hint'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1123238657

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    53 |   101   (1)| 00:00:02 |
|*  1 |  HASH JOIN         |      |     1 |    53 |   101   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    32 |    68   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT | 30000 |   615K|    32   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$73112EEC" "D"@"SEL$2")
      LEADING(@"SEL$73112EEC" "E"@"SEL$2" "D"@"SEL$2")
      FULL(@"SEL$73112EEC" "D"@"SEL$2")
      FULL(@"SEL$73112EEC" "E"@"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$64EAE176")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$64EAE176")
      OUTLINE_LEAF(@"SEL$73112EEC")
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

急ぎ足で書いたので、近いうちに見直します。

8
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
8
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?