この記事はOracle Database 19cの新機能ヒント・レポートを試していてわかった点を記述しています。ヒント・レポートについては ヒントレポート機能を試す に書いています。
前提条件
ヒントを指定しない場合、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ヒントの矛盾をチェックし、その後でインデックス名の有効性をチェックことがわかります。