ヒントの不思議な挙動を確認する（Oracle Database 19c)

　この記事はOracle Database 19cの新機能ヒント・レポートを試していてわかった点を記述しています。ヒント・レポートについては [ヒントレポート機能を試す](https://qiita.com/plusultra/items/f000dce82266747fce81) に書いています。

# 前提条件
 ヒントを指定しない場合、IDX2_DATA1インデックスを使用するSQL文とテーブルを使います。

```
SQL> SELECT * FROM data1 WHERE c1=100;

        C1 C2
---------- ----------
       100 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  19pna1hh3f31q, child number 0
-------------------------------------
SELECT * FROM data1 WHERE c1=100

Plan hash value: 2511040045

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     3 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX2_DATA1 |     1 |    11 |     3   (0)| 00:00:01 |

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

   1 - access("C1"=100)


18 rows selected.
```

# 不正なヒントを記述

以下のSQL文を実行してヒントレポートを確認します。

```
SELECT /*+ ABC INDEX(data1 idx1_data1) FULL() */ * FROM data1 WHERE c1=100;
```

| ヒント | 説明|
| :--- | :--- |
| ABC | ヒントではなくただのコメント |
| INDEX | インデックスを指定する正常なヒント |
| FULL | 全件検索を行うヒントだがテーブル名が書いていないので不正なヒント |

```
SQL> SELECT /*+ ABC INDEX(data1 idx1_data1) FULL() */ * FROM data1 WHERE c1=100;

        C1 C2
---------- ----------
       100 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'+HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2nrqbg8my9vrg, child number 0
-------------------------------------
SELECT /*+ ABC INDEX(data1 idx1_data1) FULL() */ * FROM data1 WHERE
c1=100

Plan hash value: 2076263317

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATA1      |     1 |    11 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX1_DATA1 |     1 |       |     3   (0)| 00:00:01 |

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

   2 - access("C1"=100)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (E - Syntax error (2))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  ABC
         E -  FULL(

   1 -  SEL$1 / DATA1@SEL$1
           -  INDEX(data1 idx1_data1)

31 rows selected.
```

ヒント・レポートを確認すると、ABCとFULLは Syntex Errorとされており、INDEXヒントは有効であることがわかります（IDX1_DATA1 が使われている）。

# ヒントの順番を変える

使っているヒントの構文は同じですが、ヒントの順番を変えてみます。先に無効なFULLヒントを記述し、有効なINDEXヒントを後に記述します。

```
SELECT /*+ ABC FULL() INDEX(data1 idx1_data1) */ * FROM data1 WHERE c1=100;
```


```
SQL> SELECT /*+ ABC FULL() INDEX(data1 idx1_data1) */ * FROM data1 WHERE c1=100;

        C1 C2
---------- ----------
       100 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'+HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bbxxffbwnhh93, child number 0
-------------------------------------
SELECT /*+ ABC FULL() INDEX(data1 idx1_data1) */ * FROM data1 WHERE
c1=100

Plan hash value: 2511040045

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     3 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX2_DATA1 |     1 |    11 |     3   (0)| 00:00:01 |

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

   1 - access("C1"=100)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (E - Syntax error (2))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  ABC
         E -  FULL(
```

ヒント・レポートを確認すると、ABCとFULLは Syntex Errorとされているところは同じですが、
INDEXヒントはヒントとして認識されていないことがわかります。このため、有効なヒントは記述されていないということになり、使われるインデックスもデフォルトの IDX2_DATA1 になっています。ヒントの解析を途中であきらめているような動作です。

# 矛盾するヒントを与えてみる。

同一テーブルに対する FULL ヒントと、INDEX ヒントは相反する指定であるため、記述が正しければどちらも使われません。しかしINDEXヒントには存在しないインデックス名を与えてみます。

```
SELECT /*+ FULL(data1) INDEX(data1 not_exists_index1) */ * FROM data1 WHERE c1=100;
```


```
SQL> SELECT /*+ FULL(data1) INDEX(data1 not_exists_index1) */ * FROM data1 WHERE c1=100;

        C1 C2
---------- ----------
       100 data1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>'+HINT_REPORT'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  g3qq7hk22xg8a, child number 0
-------------------------------------
SELECT /*+ FULL(data1) INDEX(data1 not_exists_index1) */ * FROM data1
WHERE c1=100

Plan hash value: 2511040045

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     3 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX2_DATA1 |     1 |    11 |     3   (0)| 00:00:01 |

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

   1 - access("C1"=100)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------

   1 -  SEL$1 / DATA1@SEL$1
         U -  FULL(data1) / hint conflicts with another in sibling query block
         U -  INDEX(data1 not_exists_indes1) / hint conflicts with another in sibling query block

27 rows selected.
```

INDEXヒントだけが無効になるかと思いましたが、FULLヒントまで無効になっています。
まず同一テーブルのFULLとINDEXヒントの矛盾をチェックし、その後でインデックス名の有効性をチェックことがわかります。