タグ別アーカイブ: CBO

INとEXISTSはどちらが速いのか?

問題:「SALARY > 10000」となる社員が所属している部署を表示せよ

今回は、セミジョイン(セミ結合)について考えてみたい。

セミジョインは通常のジョイントと異なり、2つのクエリー間に親子(主従)関係があるのが特徴である。つまり、メインクエリーがあってそれに従属するサブクエリーから成るのがセミジョインで、ジョインにおいて2つのクエリー(テーブル、ビュー)が(実行順はあるが)対等関係にあるのとは明確に異なる。

Oracleでは、IN述語EXISTS述語を使用するものをセミジョイン(セミ結合)NOT IN述語NOT EXISTS述語を使用するものをアンチジョイン(アンチ結合)と呼んでいる。(リンクは12cR1 SQLチューニングガイドの該当箇所)

使用するテーブル

今回はHRサンプルスキーマのDEPARTMENTS(部署)表とEMPLOYEES(社員)表を使ったクエリーを考えてみる。

SQL> desc DEPARTMENTS
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

SQL> desc EMPLOYEES
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

実行環境は

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

である。

1. IN述語を使ったSQL

1番目はIN述語を使ったSQLである。

DEPARTMENTS表にアクセスするメインクエリーに対して、EMPLOYEES表にアクセスするサブクエリーをIN述語で連結する。CBOが正しく判断できるようにサブクエリーはカッコで囲む。

このSQLを記述通りに解釈すると、最初にサブクエリーが実行され条件を満たすDEPARTMENT_IDの集合が作られ(このサブクエリーのみの実行では重複が発生することに注意)メインクエリーで使用するINリストが作成される。メインクエリーはこのINリストを使用してDEPARTMENTS表から必要な情報を取得する。

select
   DEPARTMENT_ID
  ,DEPARTMENT_NAME
from
  DEPARTMENTS
where
  DEPARTMENT_ID in (
    select
      DEPARTMENT_ID
    from
      EMPLOYEES
    where
      SALARY > 10000
  )
;

2. EXISTS述語を使ったSQL

次はEXISTS述語を使ったSQLである。

このSQLは(文字通りの解釈では)前項と逆でメインクエリーが先に実行される。次にメインクエリーの結果セットの各行に対してサブクエリー側で条件に合致するかを判定する。

条件に合致すれば(真:TRUE)結果セットに残り、合致しなければ(偽:FALSE)結果セットから除外される。つまり、サブクエリーはメインクエリーのフィルタとして機能する。

メインクエリーとサブクエリーの関係は「D.DEPARTMENT_ID = E.DEPARTMENT_ID」の条件で絞り込む必要がある。万一この条件を書き忘れると、EMPLOYEES表の中にDEPARTMENTS表に存在するDEPARTMENT_IDが1つでも存在すれば、EXISTS述語が常に真(TRUE)となりDEPARTMENTS表の全行が返ることになるので、肝心の「SALARY > 10000」という条件が効かない結果となってしまう。

select
   DEPARTMENT_ID
  ,DEPARTMENT_NAME
from
  DEPARTMENTS D
where
  exists (
    select
      *
    from
      EMPLOYEES E
    where
      D.DEPARTMENT_ID = E.DEPARTMENT_ID
    and SALARY > 10000
  )
;

3. 内部結合を使ったSQL

セミジョインのSQLは次のような内部結合を使ったSQLに書き換えることができる。(書き換えることができるというのは違うSQLでも同じ結果を返す、という意味である。)

注意点としては、DISTINCT句忘れてはいけないことである。(DISTINCTが必要な理由は最後に示す。)

select distinct
   D.DEPARTMENT_ID
  ,D.DEPARTMENT_NAME
from
  DEPARTMENTS D
  inner join EMPLOYEES E
  on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
where
  E.SALARY > 10000
;

どの書き方が優れているのか?

このように同じ結果を得る(であろう)3つのSQLを紹介したのだが、実際はどの書き方がより優れているのだろうか?
違いがあるのであれば、開発者はどんな点に注意した方がよいのだろうか?

性能的な優劣を比較するのであれば、大量データを使用して実行時間を比較するのが王道であるのだが、サンプル表を使用しての簡単な検証になるので、今回は実行計画を確認しながら実行する。

1. IN述語を使ったSQL

		  
SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS
  6  where
  7    DEPARTMENT_ID in (
  8      select
  9        DEPARTMENT_ID
 10      from
 11        EMPLOYEES
 12      where
 13        SALARY > 10000
 14    )
 15  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
   3 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

今回の問題に対する解答は、上記6つの部署となることがわかる。

細かい考察は後にしてEXISTS述語を使ったSQLも同様に見てみよう。

2. EXISTS述語を使ったSQL

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists (
  8      select
  9        *
 10      from
 11        EMPLOYEES E
 12      where
 13        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 14      and SALARY > 10000
 15    )
 16  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

2つの実行計画を比較するには「Plan hash value」を見ればよいのだが、何と同じ「2317224448」となっていることがわかる。

つまり、セミジョインはどちらの書き方をしても同じ実行計画が選択されるという興味深い結果が明らかになった。

この実行計画はHASH JOINなので、メインクエリーのDEPARTMENTS表がビルド表となり、サブクエリーのEMPLOYEES表がプローブ表となっている。

Id=1のオペレーションは「HASH JOIN SEMI」であるが、対応する述語情報(Predicate Information)は

1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

となっている。
1.の書き方では、このような結合条件を記述していないにもかかわらず、CBOは裏でちゃんとこのような結合条件を使ったセミジョインを考慮してくれている。

従って、素直に解釈するとOracle12cでは「1. IN述語を使ったSQL」でも内部的には「2. EXISTS述語を使ったSQL」にリライトされているように見える。

ただし、マニュアル(SQLチューニングガイド)の記述を見ると、セミジョイン、アンチジョインは内部的には結合(ジョイン)タイプとして処理されると説明されている。(「…セミ結合とアンチ結合は、それらを実行するSQL構文が副問合せであっても、結合タイプとして考慮されます。これらは、副問合せ構文を結合形式で解決できるようにフラット化するため、オプティマイザによって使用される内部アルゴリズムです。…」)

3. 内部結合を使ったSQL

ここまで来ると、残りの内部結合を使ったSQLも気になる。ひょっとして同じ実行計画となるのであろうか?

SQL> select distinct
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6    inner join EMPLOYEES E
  7    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  8  where
  9    E.SALARY > 10000
 10  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
          100 Finance
           90 Executive
           30 Purchasing
          110 Accounting
           80 Sales
           20 Marketing

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1983137394

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    11 |   253 |     7  (15)| 00:00:01 |
|   1 |  HASH UNIQUE        |             |    11 |   253 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   4 - filter("E"."SALARY">10000)


統計
----------------------------------------------------------
        302  recursive calls
          4  db block gets
        324  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          6  rows processed

今度は違う実行計画となった。(Plan hash value: 1983137394)

このクエリーはセミジョインでないにもかかわらず、Id=2で「HASH JOIN SEMI」となっているのが興味深い。

さらに、この実行計画ではId=1の「HASH UNIQUE」が実行されている。
つまり、セミジョインに比べてDISTINCT付きジョインは、オペレーションが1つ多くなっている分性能的に不利なのではないかと思われる。(あくまでも実行計画を比較した上での見解)

DISTINCT句を外してみると

参考までに、上のクエリーでDISTINCT句を外して実行してみる。
(重複排除すべき行を網掛け表示にしてある。)

SQL> select
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6    inner join EMPLOYEES E
  7    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  8  where
  9    E.SALARY > 10000
 10  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
           90 Executive
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           80 Sales
           20 Marketing
          110 Accounting

15行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    68 |  1564 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    68 |  1564 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("E"."SALARY">10000)


統計
----------------------------------------------------------
          4  recursive calls
          4  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
        860  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

DISTINCT句を付けないと「HASH JOIN」となることがわかる。

セミジョインの結合方式を考える

上記セミジョインの結合方式は、「HASH JOIN SEMI」というオペレーション名からハッシュ型セミジョインであることがわかるが、以下のようにサブクエリー側にヒント句を使用することで結合方式を変更することができる。

ネステッドループ型セミジョイン

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ NL_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           20 Marketing
           30 Purchasing
           80 Sales
           90 Executive
          100 Finance
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2332702268

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |    41   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |             |    11 |   253 |    41   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    28 |   196 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
         56  db block gets
        220  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

以前のバージョンでは、セミジョインのデフォルトの結合方式であったが、この検証においては3つの中でも最もコストが高くなった。

細かいが、結果がDEPARTMENT_IDの昇順となっている点がハッシュ型と異なっていることがわかる。

ソートマージ型セミジョイン

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ MERGE_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           20 Marketing
           30 Purchasing
           80 Sales
           90 Executive
          100 Finance
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2249117780

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    11 |   253 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN SEMI    |             |    11 |   253 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |             |    27 |   432 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |             |    68 |   476 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   4 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   5 - filter("SALARY">10000)


統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        778  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          6  rows processed

ソートマージ型は、オペレーション数が一番多いにもかかわらず、ネステッドループ型よりもコストが低い。

結果はネステッドループ型と同様にDEPARTMENT_IDの昇順になっている。

セミジョインはサブクエリーによるメインクエリーの存在チェック(フィルタリング)とも言えるが、単純な有無を判定するのであれば並び替えをせずにハッシュテーブル上で比較ができるハッシュ型が有利なのではないかと考える。

筆者の経験では、DBリンク越しのセミジョイン(サブクエリー側のテーブルがリモート表)のあるSQLがネステッドループ型セミジョインとなっていたので、HASH_SJヒントでハッシュ型セミジョインにしたところ、2時間経っても終わらないクエリーがわずか2分強で終了するまでに改善したことがある。

ちなみにHASH_SJヒントは以下のように使用する。

SQL> select
  2     DEPARTMENT_ID
  3    ,DEPARTMENT_NAME
  4  from
  5    DEPARTMENTS D
  6  where
  7    exists(
  8      select
  9        /*+ HASH_SJ */
 10        *
 11      from
 12        EMPLOYEES E
 13      where
 14        D.DEPARTMENT_ID = E.DEPARTMENT_ID
 15      and SALARY > 10000
 16    )
 17  ;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------
           90 Executive
          100 Finance
           30 Purchasing
           80 Sales
           20 Marketing
          110 Accounting

6行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   253 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |             |    11 |   253 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |    68 |   476 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   3 - filter("SALARY">10000)

おまけ:内部結合になぜDISTINCT句が必要なのか?

今回のようなSQLで内部結合を使う場合DISTINCT句が必要なことは先に述べたが、その理由を考えてみたい。

結合(ジョイン)は2つのテーブルから結果を取得するものであるが、「部署を表示せよ」ということであれば片方のテーブルに属するカラムのみを表示させることになる。

問題の主旨からすれば本来は「DEPARTMENT_NAME」のみを取得するだけでよかったのだが、今回はわかりやすくするために「DEPARTMENT_NAME」が従属する主キーである「DEPARTMENT_ID」も表示するようにした。

同様に、「SALARY > 10000」という条件で絞り込んだEMPLOYEES表を結合することを考えると、各行の主キーである「EMPLOYEE_ID」も一緒に結合すると考えることができる。

これを実際のクエリーで示したのが以下となる。

SQL> select
  2     D.DEPARTMENT_ID
  3    ,D.DEPARTMENT_NAME
  4    ,E.EMPLOYEE_ID "(EMPLOYEE_ID)"
  5  from
  6    DEPARTMENTS D
  7    inner join EMPLOYEES E
  8    on  D.DEPARTMENT_ID = E.DEPARTMENT_ID
  9  where
 10    E.SALARY > 10000
 11  ;

DEPARTMENT_ID DEPARTMENT_NAME (EMPLOYEE_ID)
------------- --------------- -------------
           20 Marketing                 201
           30 Purchasing                114
           80 Sales                     145
           80 Sales                     146
           80 Sales                     147
           80 Sales                     148
           80 Sales                     149
           80 Sales                     162
           80 Sales                     168
           80 Sales                     174
           90 Executive                 100
           90 Executive                 101
           90 Executive                 102
          100 Finance                   108
          110 Accounting                205

15行が選択されました。

従って、このクエリーからEMPLOYEE_IDを非表示(SELECTリストから外す)としても、DISTINCT句を付けない限り結果は重複表示される。

これが、DISTINCT句付き内部結合としなければならない理由である。

まとめ

今回のタイトルは「INとEXISTSはどちらが速いのか?」にしてみたが、開発の現場ではこのような疑問が生じることが多々あるかと思う。

どちらでも結果が変わらない記述法があると「果たしてどちらがいいのか?」という議論になり、あまり望ましくない結論として「どちらかに統一してしまえ」ということになったりする。

「実行計画が変わらないのであればどちらでもよいではないか?」それはそれで問題ないのであるが、次のようなやっかいなトラブルが起きる可能性がある。

昔のバージョンにおいてEXISTS述語にすることで大きく性能改善させた成功体験のあるベテランSEがいたとする。

新人SEが一生懸命IN述語で書いてきたSQL文を見て、ベテランSEが「INなんかダメだ!EXISTSに書き直せ!」などと安直に指示したりすると、新人SEは一括置換でEXISTSに直したりするかもしれない。

本文でも紹介したように、EXISTSの場合はサブクエリーに結合条件を記述しなければならないのに、それがスッポリ抜け落ちてしまう可能性があり、しかも構文エラーにならないので間違いに気づかない危険性もある。

どちらかに統一するような不毛な議論をするのではなく、どちらでもよいのだという柔軟さを持つべきなのではないだろうか。

CBOは日々進化しており、以前の常識が通用しないかもしれないということをベテランは自覚し、最新バージョンではどうなっているのかという疑問を常に持ち続ける必要があるのではないかと、自戒を込めて主張したい。

Oracleバージョンによるヒント句の変遷〜最新版〜

オンプレミス版Oracle12c R2リリース!

OTNでOracle12c R2がダウンロード出来るようになったので早速手元環境にインストールしてみた。

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

新しく追加されたヒント句を確認する

以前12cR1までのヒント句の変遷を追ってみたことがある。Oracleバージョンによるヒント句の変遷 参照

今回のバージョンアップで新たに追加されたヒント句を確認してみよう。

SQL> SELECT VERSION,NAME HINT_NAME,INVERSE,CLASS,SQL_FEATURE
  2  FROM V$SQL_HINT
  3  ORDER BY
  4   TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(VERSION,'\.','',1,2),'\.','',1,2),99.999) DESC
  5  ,CLASS,NAME;

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.2.0.1  BUSHY_JOIN                     NO_BUSHY_JOIN                  BUSHY_JOIN                     QKSFM_BUSHY_JOIN
          NO_BUSHY_JOIN                  BUSHY_JOIN                     BUSHY_JOIN                     QKSFM_BUSHY_JOIN
          CONTAINERS                                                    CONTAINERS                     QKSFM_ALL
          DATA_VALIDATE                                                 DATA_VALIDATE                  QKSFM_EXECUTION
          DIST_AGG_PROLLUP_PUSHDOWN      NO_DIST_AGG_PROLLUP_PUSHDOWN   DIST_AGG_PROLLUP_PUSHDOWN      QKSFM_PQ
          NO_DIST_AGG_PROLLUP_PUSHDOWN   DIST_AGG_PROLLUP_PUSHDOWN      DIST_AGG_PROLLUP_PUSHDOWN      QKSFM_PQ
          ELIMINATE_SQ                   NO_ELIMINATE_SQ                ELIMINATE_SQ                   QKSFM_ELIMINATE_SQ
          NO_ELIMINATE_SQ                ELIMINATE_SQ                   ELIMINATE_SQ                   QKSFM_ELIMINATE_SQ
          FRESH_MV                                                      FRESH_MV                       QKSFM_MVIEWS
          ORDER_SUBQ                                                    ORDER_SUBQ                     QKSFM_TRANSFORMATION
          NO_OR_EXPAND                   OR_EXPAND                      OR_EXPAND                      QKSFM_CBQT_OR_EXPANSION
          OR_EXPAND                      NO_OR_EXPAND                   OR_EXPAND                      QKSFM_CBQT_OR_EXPANSION
          SQL_SCOPE                                                     SQL_SCOPE                      QKSFM_COMPILATION
          NO_USE_DAGG_UNION_ALL_GSETS    USE_DAGG_UNION_ALL_GSETS       USE_DAGG_UNION_ALL_GSETS       QKSFM_GROUPING_SET_XFORM
          USE_DAGG_UNION_ALL_GSETS       NO_USE_DAGG_UNION_ALL_GSETS    USE_DAGG_UNION_ALL_GSETS       QKSFM_GROUPING_SET_XFORM
          NO_USE_HASH_GBY_FOR_DAGGPSHD   USE_HASH_GBY_FOR_DAGGPSHD      USE_HASH_GBY_FOR_DAGGPSHD      QKSFM_ALL
          USE_HASH_GBY_FOR_DAGGPSHD      NO_USE_HASH_GBY_FOR_DAGGPSHD   USE_HASH_GBY_FOR_DAGGPSHD      QKSFM_ALL
          NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT    USE_PARTITION_WISE_DISTINCT    QKSFM_PARTITION
          USE_PARTITION_WISE_DISTINCT    NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT    QKSFM_PARTITION
          NO_USE_PARTITION_WISE_GBY      USE_PARTITION_WISE_GBY         USE_PARTITION_WISE_GBY         QKSFM_PARTITION
          USE_PARTITION_WISE_GBY         NO_USE_PARTITION_WISE_GBY      USE_PARTITION_WISE_GBY         QKSFM_PARTITION
          XMLTSET_DML_ENABLE                                            XMLTSET_DML_ENABLE             QKSFM_ALL
********* ------------------------------
count                                 22

12cR2では新たに22個のヒント句が追加された。(トータルでは352個)

12cR1までのヒント一覧

12cR1までのヒント句を以下に再掲する。(バージョンの降順)

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.1.0.2  ADAPTIVE_PLAN                  NO_ADAPTIVE_PLAN               ADAPTIVE_PLAN                  QKSFM_ADAPTIVE_PLAN
          NO_ADAPTIVE_PLAN               ADAPTIVE_PLAN                  ADAPTIVE_PLAN                  QKSFM_ADAPTIVE_PLAN
          ANSI_REARCH                    NO_ANSI_REARCH                 ANSI_REARCH                    QKSFM_ANSI_REARCH
          NO_ANSI_REARCH                 ANSI_REARCH                    ANSI_REARCH                    QKSFM_ANSI_REARCH
          ELIM_GROUPBY                   NO_ELIM_GROUPBY                ELIM_GROUPBY                   QKSFM_TRANSFORMATION
          NO_ELIM_GROUPBY                ELIM_GROUPBY                   ELIM_GROUPBY                   QKSFM_TRANSFORMATION
          INMEMORY                       NO_INMEMORY                    INMEMORY                       QKSFM_EXECUTION
          NO_INMEMORY                    INMEMORY                       INMEMORY                       QKSFM_EXECUTION
          INMEMORY_PRUNING               NO_INMEMORY_PRUNING            INMEMORY_PRUNING               QKSFM_EXECUTION
          NO_INMEMORY_PRUNING            INMEMORY_PRUNING               INMEMORY_PRUNING               QKSFM_EXECUTION
          RESERVOIR_SAMPLING                                            RESERVOIR_SAMPLING             QKSFM_EXECUTION
          NO_USE_VECTOR_AGGREGATION      USE_VECTOR_AGGREGATION         USE_VECTOR_AGGREGATION         QKSFM_VECTOR_AGG
          USE_VECTOR_AGGREGATION         NO_USE_VECTOR_AGGREGATION      USE_VECTOR_AGGREGATION         QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM            VECTOR_TRANSFORM               VECTOR_TRANSFORM               QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM               NO_VECTOR_TRANSFORM            VECTOR_TRANSFORM               QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM_DIMS       VECTOR_TRANSFORM_DIMS          VECTOR_TRANSFORM_DIMS          QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM_DIMS          NO_VECTOR_TRANSFORM_DIMS       VECTOR_TRANSFORM_DIMS          QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM_FACT       VECTOR_TRANSFORM_FACT          VECTOR_TRANSFORM_FACT          QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM_FACT          NO_VECTOR_TRANSFORM_FACT       VECTOR_TRANSFORM_FACT          QKSFM_VECTOR_AGG
********* ------------------------------
count                                 19

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.1.0.1  CUBE_AJ                                                       ANTIJOIN                       QKSFM_JOIN_METHOD
          AUTO_REOPTIMIZE                NO_AUTO_REOPTIMIZE             AUTO_REOPTIMIZE                QKSFM_AUTO_REOPT
          NO_AUTO_REOPTIMIZE             AUTO_REOPTIMIZE                AUTO_REOPTIMIZE                QKSFM_AUTO_REOPT
          BATCH_TABLE_ACCESS_BY_ROWID    NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID    QKSFM_EXECUTION
          NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID    BATCH_TABLE_ACCESS_BY_ROWID    QKSFM_EXECUTION
          BITMAP_AND                                                    BITMAP_AND                     QKSFM_BITMAP_TREE
          CLUSTERING                     NO_CLUSTERING                  CLUSTERING                     QKSFM_CLUSTERING
          NO_CLUSTERING                  CLUSTERING                     CLUSTERING                     QKSFM_CLUSTERING
          CLUSTER_BY_ROWID               NO_CLUSTER_BY_ROWID            CLUSTER_BY_ROWID               QKSFM_CBO
          NO_CLUSTER_BY_ROWID            CLUSTER_BY_ROWID               CLUSTER_BY_ROWID               QKSFM_CBO
          DATA_SECURITY_REWRITE_LIMIT    NO_DATA_SECURITY_REWRITE       DATA_SECURITY_REWRITE_LIMIT    QKSFM_DATA_SECURITY_REWRITE
          NO_DATA_SECURITY_REWRITE       DATA_SECURITY_REWRITE_LIMIT    DATA_SECURITY_REWRITE_LIMIT    QKSFM_DATA_SECURITY_REWRITE
          DECORRELATE                    NO_DECORRELATE                 DECORRELATE                    QKSFM_DECORRELATE
          NO_DECORRELATE                 DECORRELATE                    DECORRELATE                    QKSFM_DECORRELATE
          GATHER_OPTIMIZER_STATISTICS    NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS    QKSFM_DBMS_STATS
          NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS    GATHER_OPTIMIZER_STATISTICS    QKSFM_DBMS_STATS
          NO_USE_CUBE                    USE_CUBE                       JOIN                           QKSFM_USE_CUBE
          USE_CUBE                       NO_USE_CUBE                    JOIN                           QKSFM_USE_CUBE
          NO_PARTIAL_JOIN                PARTIAL_JOIN                   PARTIAL_JOIN                   QKSFM_PARTIAL_JOIN
          PARTIAL_JOIN                   NO_PARTIAL_JOIN                PARTIAL_JOIN                   QKSFM_PARTIAL_JOIN
          NO_PARTIAL_ROLLUP_PUSHDOWN     PARTIAL_ROLLUP_PUSHDOWN        PARTIAL_ROLLUP_PUSHDOWN        QKSFM_PQ
          PARTIAL_ROLLUP_PUSHDOWN        NO_PARTIAL_ROLLUP_PUSHDOWN     PARTIAL_ROLLUP_PUSHDOWN        QKSFM_PQ
          NO_PQ_CONCURRENT_UNION         PQ_CONCURRENT_UNION            PQ_CONCURRENT_UNION            QKSFM_PQ
          PQ_CONCURRENT_UNION            NO_PQ_CONCURRENT_UNION         PQ_CONCURRENT_UNION            QKSFM_PQ
          PQ_DISTRIBUTE_WINDOW                                          PQ_DISTRIBUTE_WINDOW           QKSFM_PQ
          PQ_FILTER                                                     PQ_FILTER                      QKSFM_PQ
          NO_PQ_REPLICATE                PQ_REPLICATE                   PQ_REPLICATE                   QKSFM_PQ_REPLICATE
          PQ_REPLICATE                   NO_PQ_REPLICATE                PQ_REPLICATE                   QKSFM_PQ_REPLICATE
          NO_PQ_SKEW                     PQ_SKEW                        PQ_SKEW                        QKSFM_PQ
          PQ_SKEW                        NO_PQ_SKEW                     PQ_SKEW                        QKSFM_PQ
          NO_PX_FAULT_TOLERANCE          PX_FAULT_TOLERANCE             PX_FAULT_TOLERANCE             QKSFM_PQ
          PX_FAULT_TOLERANCE             NO_PX_FAULT_TOLERANCE          PX_FAULT_TOLERANCE             QKSFM_PQ
          CUBE_SJ                                                       SEMIJOIN                       QKSFM_JOIN_METHOD
          USE_HIDDEN_PARTITIONS                                         USE_HIDDEN_PARTITIONS          QKSFM_PARTITION
          WITH_PLSQL                                                    WITH_PLSQL                     QKSFM_ALL
          NO_ZONEMAP                     ZONEMAP                        ZONEMAP                        QKSFM_ZONEMAP
          ZONEMAP                        NO_ZONEMAP                     ZONEMAP                        QKSFM_ZONEMAP
********* ------------------------------
count                                 37

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.4  DISABLE_PARALLEL_DML           ENABLE_PARALLEL_DML            ENABLE_PARALLEL_DML            QKSFM_DML
          ENABLE_PARALLEL_DML            DISABLE_PARALLEL_DML           ENABLE_PARALLEL_DML            QKSFM_DML
********* ------------------------------
count                                  2

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.3  FULL_OUTER_JOIN_TO_OUTER       NO_FULL_OUTER_JOIN_TO_OUTER    FULL_OUTER_JOIN_TO_OUTER       QKSFM_CBO
          NO_FULL_OUTER_JOIN_TO_OUTER    FULL_OUTER_JOIN_TO_OUTER       FULL_OUTER_JOIN_TO_OUTER       QKSFM_CBO
          NO_SEMI_TO_INNER               SEMI_TO_INNER                  NO_SEMI_TO_INNER               QKSFM_CBO
          NO_OUTER_JOIN_TO_ANTI          OUTER_JOIN_TO_ANTI             OUTER_JOIN_TO_ANTI             QKSFM_CBO
          OUTER_JOIN_TO_ANTI             NO_OUTER_JOIN_TO_ANTI          OUTER_JOIN_TO_ANTI             QKSFM_CBO
          SEMI_TO_INNER                  NO_SEMI_TO_INNER               SEMI_TO_INNER                  QKSFM_CBO
********* ------------------------------
count                                  6

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.2  NO_TABLE_LOOKUP_BY_NL          TABLE_LOOKUP_BY_NL             TABLE_LOOKUP_BY_NL             QKSFM_TABLE_LOOKUP_BY_NL
          TABLE_LOOKUP_BY_NL             NO_TABLE_LOOKUP_BY_NL          TABLE_LOOKUP_BY_NL             QKSFM_TABLE_LOOKUP_BY_NL
          NO_USE_HASH_GBY_FOR_PUSHDOWN   USE_HASH_GBY_FOR_PUSHDOWN      USE_HASH_GBY_FOR_PUSHDOWN      QKSFM_ALL
          USE_HASH_GBY_FOR_PUSHDOWN      NO_USE_HASH_GBY_FOR_PUSHDOWN   USE_HASH_GBY_FOR_PUSHDOWN      QKSFM_ALL
          NO_XDB_FASTPATH_INSERT         XDB_FASTPATH_INSERT            XDB_FASTPATH_INSERT            QKSFM_ALL
          XDB_FASTPATH_INSERT            NO_XDB_FASTPATH_INSERT         XDB_FASTPATH_INSERT            QKSFM_ALL
********* ------------------------------
count                                  6

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.1  APPEND_VALUES                  NOAPPEND                       APPEND_VALUES                  QKSFM_CBO
          COALESCE_SQ                    NO_COALESCE_SQ                 COALESCE_SQ                    QKSFM_COALESCE_SQ
          NO_COALESCE_SQ                 COALESCE_SQ                    COALESCE_SQ                    QKSFM_COALESCE_SQ
          CONNECT_BY_ELIM_DUPS           NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS           QKSFM_ALL
          NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS           CONNECT_BY_ELIM_DUPS           QKSFM_ALL
          DST_UPGRADE_INSERT_CONV        NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV        QKSFM_ALL
          NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV        DST_UPGRADE_INSERT_CONV        QKSFM_ALL
          EXPAND_TABLE                   NO_EXPAND_TABLE                EXPAND_TABLE                   QKSFM_TABLE_EXPANSION
          NO_EXPAND_TABLE                EXPAND_TABLE                   EXPAND_TABLE                   QKSFM_TABLE_EXPANSION
          FACTORIZE_JOIN                 NO_FACTORIZE_JOIN              FACTORIZE_JOIN                 QKSFM_JOINFAC
          NO_FACTORIZE_JOIN              FACTORIZE_JOIN                 FACTORIZE_JOIN                 QKSFM_JOINFAC
          NO_SUBSTRB_PAD                                                NO_SUBSTRB_PAD                 QKSFM_EXECUTION
          NO_PLACE_DISTINCT              PLACE_DISTINCT                 PLACE_DISTINCT                 QKSFM_DIST_PLCMT
          PLACE_DISTINCT                 NO_PLACE_DISTINCT              PLACE_DISTINCT                 QKSFM_DIST_PLCMT
          NO_STATEMENT_QUEUING           STATEMENT_QUEUING              STATEMENT_QUEUING              QKSFM_PARALLEL
          STATEMENT_QUEUING              NO_STATEMENT_QUEUING           STATEMENT_QUEUING              QKSFM_PARALLEL
          NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG         TRANSFORM_DISTINCT_AGG         QKSFM_TRANSFORMATION
          TRANSFORM_DISTINCT_AGG         NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG         QKSFM_TRANSFORMATION
          XMLINDEX_SEL_IDX_TBL                                          XMLINDEX_SEL_IDX_TBL           QKSFM_ALL
********* ------------------------------
count                                 19

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.1.0.7  BIND_AWARE                     NO_BIND_AWARE                  BIND_AWARE                     QKSFM_CURSOR_SHARING
          NO_BIND_AWARE                  BIND_AWARE                     BIND_AWARE                     QKSFM_CURSOR_SHARING
          CHANGE_DUPKEY_ERROR_INDEX                                     CHANGE_DUPKEY_ERROR_INDEX      QKSFM_DML
          IGNORE_ROW_ON_DUPKEY_INDEX                                    IGNORE_ROW_ON_DUPKEY_INDEX     QKSFM_DML
          RETRY_ON_ROW_CHANGE                                           RETRY_ON_ROW_CHANGE            QKSFM_DML
********* ------------------------------
count                                  5

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.1.0.6  INDEX_RS_ASC                                                  ACCESS                         QKSFM_INDEX_RS_ASC
          INDEX_RS_DESC                                                 ACCESS                         QKSFM_INDEX_RS_DESC
          NLJ_BATCHING                   NO_NLJ_BATCHING                ACCESS                         QKSFM_EXECUTION
          NLJ_PREFETCH                   NO_NLJ_PREFETCH                ACCESS                         QKSFM_EXECUTION
          NO_NLJ_BATCHING                NLJ_BATCHING                   ACCESS                         QKSFM_EXECUTION
          NO_NLJ_PREFETCH                NLJ_PREFETCH                   ACCESS                         QKSFM_EXECUTION
          CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              QKSFM_CHECK_ACL_REWRITE
          COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         QKSFM_COST_XML_QUERY_REWRITE
          DB_VERSION                                                    DB_VERSION                     QKSFM_ALL
          DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            QKSFM_CBO
          USE_MERGE_CARTESIAN                                           JOIN                           QKSFM_USE_MERGE_CARTESIAN
          MONITOR                        NO_MONITOR                     MONITOR                        QKSFM_ALL
          NO_MONITOR                     MONITOR                        MONITOR                        QKSFM_ALL
          NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           QKSFM_CHECK_ACL_REWRITE
          NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      QKSFM_COST_XML_QUERY_REWRITE
          NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         QKSFM_CBO
          NO_LOAD                                                       NO_LOAD                        QKSFM_EXECUTION
          NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER
          OUTER_JOIN_TO_INNER            NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER
          NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY
          PLACE_GROUP_BY                 NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY
          NO_RESULT_CACHE                RESULT_CACHE                   RESULT_CACHE                   QKSFM_EXECUTION
          RESULT_CACHE                   NO_RESULT_CACHE                RESULT_CACHE                   QKSFM_EXECUTION
          NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               SUBQUERY_PRUNING               QKSFM_CBO
          SUBQUERY_PRUNING               NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               QKSFM_CBO
          NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          USE_INVISIBLE_INDEXES          QKSFM_INDEX
          USE_INVISIBLE_INDEXES          NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          QKSFM_INDEX
          NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE_IN_SELECT     XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XMLINDEX_REWRITE               NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XMLINDEX_REWRITE_IN_SELECT     NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XML_DML_RWT_STMT                                              XML_DML_RWT_STMT               QKSFM_XML_REWRITE
********* ------------------------------
count                                 32

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.5  CONNECT_BY_CB_WHR_ONLY         NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY         QKSFM_TRANSFORMATION
          NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY         CONNECT_BY_CB_WHR_ONLY         QKSFM_TRANSFORMATION
          GBY_PUSHDOWN                   NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   QKSFM_ALL
          NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   GBY_PUSHDOWN                   QKSFM_ALL
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.4  CONNECT_BY_COMBINE_SW          NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          QKSFM_ALL
          NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          CONNECT_BY_COMBINE_SW          QKSFM_ALL
********* ------------------------------
count                                  2

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.3  NUM_INDEX_KEYS                                                ACCESS                         QKSFM_CBO
          NATIVE_FULL_OUTER_JOIN         NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         QKSFM_ALL
          NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         NATIVE_FULL_OUTER_JOIN         QKSFM_ALL
********* ------------------------------
count                                  3

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.2  CONNECT_BY_COST_BASED          NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION
          NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION
          CONNECT_BY_FILTERING           NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           QKSFM_ALL
          NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           CONNECT_BY_FILTERING           QKSFM_ALL
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.1  BITMAP_TREE                                                   ACCESS                         QKSFM_BITMAP_TREE
          DBMS_STATS                                                    DBMS_STATS                     QKSFM_DBMS_STATS
          ELIMINATE_JOIN                 NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 QKSFM_TABLE_ELIM
          NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 ELIMINATE_JOIN                 QKSFM_TABLE_ELIM
          ELIMINATE_OBY                  NO_ELIMINATE_OBY               ELIMINATE_OBY                  QKSFM_OBYE
          NO_ELIMINATE_OBY               ELIMINATE_OBY                  ELIMINATE_OBY                  QKSFM_OBYE
          INLINE_XMLTYPE_NT                                             INLINE_XMLTYPE_NT              QKSFM_ALL
          MODEL_COMPILE_SUBQUERY                                        MODEL_COMPILE_SUBQUERY         QKSFM_TRANSFORMATION
          MODEL_DYNAMIC_SUBQUERY                                        MODEL_DYNAMIC_SUBQUERY         QKSFM_TRANSFORMATION
          NO_CARTESIAN                                                  NO_CARTESIAN                   QKSFM_ALL
          NO_SQL_TUNE                                                   NO_SQL_TUNE                    QKSFM_ALL
          NO_XML_DML_REWRITE                                            NO_XML_DML_REWRITE             QKSFM_XML_REWRITE
          OLD_PUSH_PRED                                                 OLD_PUSH_PRED                  QKSFM_OLD_PUSH_PRED
          OPT_PARAM                                                     OPT_PARAM                      QKSFM_ALL
          OUTLINE                                                       OUTLINE                        QKSFM_ALL
          OUTLINE_LEAF                                                  OUTLINE_LEAF                   QKSFM_ALL
          PRECOMPUTE_SUBQUERY                                           PRECOMPUTE_SUBQUERY            QKSFM_TRANSFORMATION
          PRESERVE_OID                                                  PRESERVE_OID                   QKSFM_ALL
          NO_PULL_PRED                   PULL_PRED                      PULL_PRED                      QKSFM_PULL_PRED
          PULL_PRED                      NO_PULL_PRED                   PULL_PRED                      QKSFM_PULL_PRED
          NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER
          PX_JOIN_FILTER                 NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER
          RBO_OUTLINE                                                   RBO_OUTLINE                    QKSFM_RBO
          NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           USE_HASH_AGGREGATION           QKSFM_ALL
          USE_HASH_AGGREGATION           NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           QKSFM_ALL
********* ------------------------------
count                                 25

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.1.0.3  FBTSCAN                                                       FBTSCAN                        QKSFM_CBO
          GATHER_PLAN_STATISTICS                                        GATHER_PLAN_STATISTICS         QKSFM_GATHER_PLAN_STATISTICS
          IGNORE_OPTIM_EMBEDDED_HINTS                                   IGNORE_OPTIM_EMBEDDED_HINTS    QKSFM_ALL
          INCLUDE_VERSION                                               INCLUDE_VERSION                QKSFM_ALL
          MODEL_DONTVERIFY_UNIQUENESS                                   MODEL_DONTVERIFY_UNIQUENESS    QKSFM_TRANSFORMATION
          MODEL_MIN_ANALYSIS                                            MODEL_MIN_ANALYSIS             QKSFM_TRANSFORMATION
          MODEL_NO_ANALYSIS                                             MODEL_MIN_ANALYSIS             QKSFM_ALL
          MODEL_PUSH_REF                 NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 QKSFM_TRANSFORMATION
          NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 MODEL_PUSH_REF                 QKSFM_ALL
          NESTED_TABLE_FAST_INSERT                                      NESTED_TABLE_FAST_INSERT       QKSFM_ALL
          NO_INDEX_FFS                   INDEX_FFS                      NO_INDEX_FFS                   QKSFM_INDEX_FFS
          NO_INDEX_SS                    INDEX_SS                       NO_INDEX_SS                    QKSFM_INDEX_SS
          NO_PARTIAL_COMMIT                                             NO_PARTIAL_COMMIT              QKSFM_CBO
          NO_QUERY_TRANSFORMATION                                       NO_QUERY_TRANSFORMATION        QKSFM_TRANSFORMATION
          NO_USE_HASH                    USE_HASH                       NO_USE_HASH                    QKSFM_USE_HASH
          NO_USE_MERGE                   USE_MERGE                      NO_USE_MERGE                   QKSFM_USE_MERGE
          NO_USE_NL                      USE_NL                         NO_USE_NL                      QKSFM_USE_NL
          OPAQUE_TRANSFORM                                              OPAQUE_TRANSFORM               QKSFM_TRANSFORMATION
          OPAQUE_XCANONICAL                                             OPAQUE_XCANONICAL              QKSFM_TRANSFORMATION
          OPTIMIZER_FEATURES_ENABLE                                     OPTIMIZER_FEATURES_ENABLE      QKSFM_ALL
          OPT_ESTIMATE                                                  OPT_ESTIMATE                   QKSFM_OPT_ESTIMATE
          QB_NAME                                                       QB_NAME                        QKSFM_ALL
          RESTRICT_ALL_REF_CONS                                         RESTRICT_ALL_REF_CONS          QKSFM_ALL
          NO_BASETABLE_MULTIMV_REWRITE   REWRITE                        REWRITE                        QKSFM_ALL
          NO_MULTIMV_REWRITE             REWRITE                        REWRITE                        QKSFM_ALL
          REWRITE_OR_ERROR                                              REWRITE                        QKSFM_TRANSFORMATION
          NO_SET_TO_JOIN                 SET_TO_JOIN                    SET_TO_JOIN                    QKSFM_SET_TO_JOIN
          SET_TO_JOIN                    NO_SET_TO_JOIN                 SET_TO_JOIN                    QKSFM_SET_TO_JOIN
          NO_PARALLEL                    SHARED                         SHARED                         QKSFM_CBO
          SKIP_UNQ_UNUSABLE_IDX                                         SKIP_UNQ_UNUSABLE_IDX          QKSFM_CBO
          NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            STAR_TRANSFORMATION            QKSFM_STAR_TRANS
          STREAMS                                                       STREAMS                        QKSFM_CBO
          NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               SWAP_JOIN_INPUTS               QKSFM_CBO
          COLUMN_STATS                                                  TABLE_STATS                    QKSFM_STATS
          INDEX_STATS                                                   TABLE_STATS                    QKSFM_STATS
          TABLE_STATS                                                   TABLE_STATS                    QKSFM_STATS
          TRACING                                                       TRACING                        QKSFM_EXECUTION
          USE_NL_WITH_INDEX              NO_USE_NL                      USE_NL_WITH_INDEX              QKSFM_USE_NL_WITH_INDEX
          USE_WEAK_NAME_RESL                                            USE_WEAK_NAME_RESL             QKSFM_ALL
          VECTOR_READ                                                   VECTOR_READ                    QKSFM_CBO
          VECTOR_READ_TRACE                                             VECTOR_READ_TRACE              QKSFM_CBO
          X_DYN_PRUNE                                                   X_DYN_PRUNE                    QKSFM_CBO
********* ------------------------------
count                                 42

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
9.2.0     DYNAMIC_SAMPLING                                              DYNAMIC_SAMPLING               QKSFM_DYNAMIC_SAMPLING
          DYNAMIC_SAMPLING_EST_CDN                                      DYNAMIC_SAMPLING_EST_CDN       QKSFM_DYNAMIC_SAMPLING_EST_CDN
          EXPAND_GSET_TO_UNION           NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION
          NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION
          FORCE_XML_QUERY_REWRITE        NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE        QKSFM_XML_REWRITE
          NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE        FORCE_XML_QUERY_REWRITE        QKSFM_XML_REWRITE
          IGNORE_WHERE_CLAUSE                                           IGNORE_WHERE_CLAUSE            QKSFM_ALL
          NO_QKN_BUFF                                                   NO_QKN_BUFF                    QKSFM_CBO
          NO_PUSH_SUBQ                   PUSH_SUBQ                      PUSH_SUBQ                      QKSFM_TRANSFORMATION
          NO_REF_CASCADE                 REF_CASCADE_CURSOR             REF_CASCADE_CURSOR             QKSFM_CBO
          REF_CASCADE_CURSOR             NO_REF_CASCADE                 REF_CASCADE_CURSOR             QKSFM_CBO
          SYS_DL_CURSOR                                                 SYS_DL_CURSOR                  QKSFM_CBO
          SYS_RID_ORDER                                                 SYS_RID_ORDER                  QKSFM_ALL
********* ------------------------------
count                                 13

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
9.0.0     INDEX_RRS                                                     ACCESS                         QKSFM_CBO
          INDEX_SS                       NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS
          INDEX_SS_ASC                   NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS_ASC
          INDEX_SS_DESC                  NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS_DESC
          ANTIJOIN                                                      ANTIJOIN                       QKSFM_TRANSFORMATION
          BYPASS_RECURSIVE_CHECK                                        BYPASS_RECURSIVE_CHECK         QKSFM_ALL
          CARDINALITY                                                   CARDINALITY                    QKSFM_STATS
          CPU_COSTING                    NO_CPU_COSTING                 CPU_COSTING                    QKSFM_CPU_COSTING
          NO_CPU_COSTING                 CPU_COSTING                    CPU_COSTING                    QKSFM_CPU_COSTING
          CURSOR_SHARING_EXACT                                          CURSOR_SHARING_EXACT           QKSFM_CBO
          DML_UPDATE                                                    DML_UPDATE                     QKSFM_CBO
          GBY_CONC_ROLLUP                                               GBY_CONC_ROLLUP                QKSFM_TRANSFORMATION
          HWM_BROKERED                                                  HWM_BROKERED                   QKSFM_CBO
          INLINE                         MATERIALIZE                    INLINE                         QKSFM_TRANSFORMATION
          MATERIALIZE                    INLINE                         INLINE                         QKSFM_TRANSFORMATION
          LOCAL_INDEXES                                                 LOCAL_INDEXES                  QKSFM_CBO
          MV_MERGE                                                      MV_MERGE                       QKSFM_TRANSFORMATION
          NO_PRUNE_GSETS                                                NO_PRUNE_GSETS                 QKSFM_TRANSFORMATION
          OVERFLOW_NOMOVE                                               OVERFLOW_NOMOVE                QKSFM_CBO
          PQ_MAP                         PQ_NOMAP                       PQ_MAP                         QKSFM_PQ_MAP
          PQ_NOMAP                       PQ_MAP                         PQ_MAP                         QKSFM_PQ_MAP
          NO_SEMIJOIN                    SEMIJOIN                       SEMIJOIN                       QKSFM_TRANSFORMATION
          SEMIJOIN                       NO_SEMIJOIN                    SEMIJOIN                       QKSFM_TRANSFORMATION
          SKIP_EXT_OPTIMIZER                                            SKIP_EXT_OPTIMIZER             QKSFM_CBO
          SQLLDR                                                        SQLLDR                         QKSFM_CBO
          USE_TTT_FOR_GSETS                                             USE_TTT_FOR_GSETS              QKSFM_TRANSFORMATION
********* ------------------------------
count                                 26

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.6     LEADING                                                       LEADING                        QKSFM_JOIN_ORDER
          SYS_PARALLEL_TXN                                              SYS_PARALLEL_TXN               QKSFM_CBO
          NO_UNNEST                      UNNEST                         UNNEST                         QKSFM_UNNEST
          UNNEST                         NO_UNNEST                      UNNEST                         QKSFM_UNNEST
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.5     INDEX_JOIN                                                    ACCESS                         QKSFM_INDEX_JOIN
          BUFFER                         NO_BUFFER                      BUFFER                         QKSFM_CBO
          NO_BUFFER                      BUFFER                         BUFFER                         QKSFM_CBO
          BYPASS_UJVC                                                   BYPASS_UJVC                    QKSFM_CBO
          CACHE_CB                       NOCACHE                        CACHE_CB                       QKSFM_CBO
          CUBE_GB                                                       CUBE_GB                        QKSFM_CBO
          DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              DOMAIN_INDEX_SORT              QKSFM_CBO
          DOMAIN_INDEX_SORT              DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              QKSFM_CBO
          NESTED_TABLE_SET_SETID                                        NESTED_TABLE_SET_SETID         QKSFM_ALL
          NO_ACCESS                                                     NO_ACCESS                      QKSFM_ALL
          NO_INDEX                       INDEX                          NO_INDEX                       QKSFM_INDEX
          PQ_DISTRIBUTE                                                 PQ_DISTRIBUTE                  QKSFM_PQ_DISTRIBUTE
          RESTORE_AS_INTERVALS                                          RESTORE_AS_INTERVALS           QKSFM_CBO
          NO_REWRITE                     REWRITE                        REWRITE                        QKSFM_TRANSFORMATION
          REWRITE                        NO_REWRITE                     REWRITE                        QKSFM_TRANSFORMATION
          SAVE_AS_INTERVALS                                             SAVE_AS_INTERVALS              QKSFM_CBO
          SCN_ASCENDING                                                 SCN_ASCENDING                  QKSFM_ALL
********* ------------------------------
count                                 17

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.0     AND_EQUAL                                                     ACCESS                         QKSFM_AND_EQUAL
          FULL                                                          ACCESS                         QKSFM_FULL
          HASH                                                          ACCESS                         QKSFM_ALL
          INDEX_ASC                      NO_INDEX                       ACCESS                         QKSFM_INDEX_ASC
          INDEX_COMBINE                                                 ACCESS                         QKSFM_INDEX_COMBINE
          INDEX_DESC                     NO_INDEX                       ACCESS                         QKSFM_INDEX_DESC
          INDEX_FFS                                                     ACCESS                         QKSFM_INDEX_FFS
          HASH_AJ                                                       ANTIJOIN                       QKSFM_JOIN_METHOD
          MERGE_AJ                                                      ANTIJOIN                       QKSFM_JOIN_METHOD
          APPEND                         NOAPPEND                       APPEND                         QKSFM_CBO
          NOAPPEND                       APPEND                         APPEND                         QKSFM_CBO
          BITMAP                                                        BITMAP                         QKSFM_CBO
          CACHE                          NOCACHE                        CACHE                          QKSFM_EXECUTION
          NOCACHE                        CACHE                          CACHE                          QKSFM_EXECUTION
          DEREF_NO_REWRITE                                              DEREF_NO_REWRITE               QKSFM_ALL
          DRIVING_SITE                                                  DRIVING_SITE                   QKSFM_ALL
          FACT                           NO_FACT                        FACT                           QKSFM_STAR_TRANS
          NO_FACT                        FACT                           FACT                           QKSFM_STAR_TRANS
          USE_HASH                       NO_USE_HASH                    JOIN                           QKSFM_USE_HASH
          USE_MERGE                      NO_USE_MERGE                   JOIN                           QKSFM_USE_MERGE
          USE_NL                         NO_USE_NL                      JOIN                           QKSFM_USE_NL
          MERGE                          NO_MERGE                       MERGE                          QKSFM_CVM
          ALL_ROWS                                                      MODE                           QKSFM_ALL_ROWS
          CHOOSE                                                        MODE                           QKSFM_CHOOSE
          FIRST_ROWS                                                    MODE                           QKSFM_FIRST_ROWS
          RULE                                                          MODE                           QKSFM_RBO
          NESTED_TABLE_GET_REFS                                         NESTED_TABLE_GET_REFS          QKSFM_ALL
          ORDERED                                                       ORDERED                        QKSFM_CBO
          NO_EXPAND                      USE_CONCAT                     OR_EXPAND                      QKSFM_USE_CONCAT
          USE_CONCAT                     NO_EXPAND                      OR_EXPAND                      QKSFM_USE_CONCAT
          NO_PARALLEL_INDEX              PARALLEL_INDEX                 PARALLEL_INDEX                 QKSFM_PQ
          PARALLEL_INDEX                 NO_PARALLEL_INDEX              PARALLEL_INDEX                 QKSFM_PQ
          PIV_GB                                                        PIV_GB                         QKSFM_ALL
          TIV_GB                                                        PIV_GB                         QKSFM_ALL
          PIV_SSF                                                       PIV_SSF                        QKSFM_ALL
          TIV_SSF                                                       PIV_SSF                        QKSFM_ALL
          NO_PUSH_PRED                   PUSH_PRED                      PUSH_PRED                      QKSFM_FILTER_PUSH_PRED
          PUSH_PRED                      NO_PUSH_PRED                   PUSH_PRED                      QKSFM_FILTER_PUSH_PRED
          PUSH_SUBQ                      NO_PUSH_SUBQ                   PUSH_SUBQ                      QKSFM_TRANSFORMATION
          REMOTE_MAPPED                                                 REMOTE_MAPPED                  QKSFM_ALL
          HASH_SJ                                                       SEMIJOIN                       QKSFM_JOIN_METHOD
          MERGE_SJ                                                      SEMIJOIN                       QKSFM_JOIN_METHOD
          SEMIJOIN_DRIVER                                               SEMIJOIN_DRIVER                QKSFM_CBO
          NOPARALLEL                     SHARED                         SHARED                         QKSFM_PARALLEL
          SHARED                         NO_PARALLEL                    SHARED                         QKSFM_PARALLEL
          STAR                                                          STAR                           QKSFM_STAR_TRANS
          STAR_TRANSFORMATION            NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            QKSFM_STAR_TRANS
          SWAP_JOIN_INPUTS               NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               QKSFM_CBO
          USE_ANTI                                                      USE_ANTI                       QKSFM_CBO
          USE_SEMI                                                      USE_SEMI                       QKSFM_CBO
********* ------------------------------
count                                 50

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.0.0     CLUSTER                                                       ACCESS                         QKSFM_CBO
          INDEX                          NO_INDEX                       ACCESS                         QKSFM_INDEX
          QUEUE_CURR                                                    ACCESS                         QKSFM_CBO
          QUEUE_ROWP                                                    ACCESS                         QKSFM_CBO
          ROWID                                                         ACCESS                         QKSFM_CBO
          NL_AJ                                                         ANTIJOIN                       QKSFM_JOIN_METHOD
          EXPR_CORR_CHECK                                               EXPR_CORR_CHECK                QKSFM_CBO
          NO_MERGE                       MERGE                          MERGE                          QKSFM_CVM
          MERGE_CONST_ON                                                MERGE_CONST_ON                 QKSFM_CBO
          NO_MONITORING                                                 NO_MONITORING                  QKSFM_ALL
          NO_ORDER_ROLLUPS                                              NO_ORDER_ROLLUPS               QKSFM_TRANSFORMATION
          NO_STATS_GSETS                                                NO_STATS_GSETS                 QKSFM_ALL
          ORDERED_PREDICATES                                            ORDERED_PREDICATES             QKSFM_CBO
          NL_SJ                                                         SEMIJOIN                       QKSFM_JOIN_METHOD
********* ------------------------------
count                                 14

352行が選択されました。

USE_INVISIBLE_INDEXESヒントについて(続編)

不可視索引のその後

先日、不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おうという記事を書いたのだが、以下の記述に関してどうやら違う挙動となるらしいことがわかった。


INDEXヒント+USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。


具体的には、複数の不可視索引が定義してある場合、INDEXヒントで明確に指定している不可視索引以外の不可視索引も使用されるようだ。

この部分を詳細に再検証してみたいと思う。

複数の不可視索引が存在する場合を検証する

検証環境

今回の検証で使用した環境は以下の通りである。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show user
USER is "SH"

複数の索引を同時に使用するケースを考える

B*ツリー索引は、原則として1つの問合せブロックの中で1つだけ使用される。1つのSQL文の中で同時に2つ以上のB*ツリー索引を使うためには2つ以上の問合せブロックを組み合わせる必要がある。

今回の検証では、問合せ自体はなるべく簡単にしたいので、B*ツリー索引ではなくビットマップ索引を使用する。

SH.SALES表に定義してある(ビットマップ)索引の状況を確認すると以下のようになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      VISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      VISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

基本問合せ

基本となる問合せは以下のとおり。
2つの絞り込み条件により、SALES表にアクセスする。

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

2つのビットマップ索引を使い、それぞれ絞り込んだ結果を「BITMAP AND」操作(Id=4)により両方の条件を満たす集合を作り、件数に変換して結果を得ていることがわかる。(SALES表には一切アクセスしていない。)

索引SALES_CUST_BIXを不可視にする

次に、索引SALES_CUST_BIXを不可視に変更し、同じ問合せを行ってみよう。

SQL> alter index SALES_CUST_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

CUST_IDに比べ、CHANNEL_IDのカーディナリティが低いため、CUST_IDの絞り込みに索引が使えなくなった途端、実行計画はSALES表に対する全件検索へと変わっていることがわかる。

索引SALES_CHANNEL_BIXを不可視にする

引き続き、索引SALES_CHANNEL_BIXを不可視にする。

SQL> alter index SALES_CHANNEL_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

索引SALES_CUST_BIXが使用不可(不可視)となっていることで、既に実行計画は全件検索となっているので、実行計画に変化はない。

ここまでで、SALES表の索引のうち2つを不可視に変更したことになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      INVISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      INVISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

USE_INVISIBLE_INDEXESヒントを指定する(INDEXヒントは使用しない)

ここで、USE_INVISIBLE_INDEXESヒントを指定して問合せを実行してみる。
2つの不可視索引が使えるようになるので、最初と同じ実行計画となるはずである。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   count(*) from SALES
  3  where CUST_ID    = 25939
  4  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

想定通りの結果となった。

USE_INVISIBLE_INDEXESヒントとINDEXヒントを明示的に指定する

次に、INDEXヒントでSALES SALES_CUST_BIXのみの使用を明示的に指定してみる。
INDEXヒントで使用される索引を限定することが出来るのであれば、実行計画は別のものになることが予想される。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX) */
  3   count(*) from SALES
  4  where CUST_ID    = 25939
  5  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

INDEXヒントに指定した索引とは別の索引SALES_CHANNEL_BIXも使用されていることがわかる。

つまりINDEXヒントだけでは使用される索引を特定することが出来ないことがわかった。

使用しない索引をNO_INDEXヒントで明示する

使用したくない方の不可視索引を明示的に指定するには、以下のようにNO_INDEXヒントを使う。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX)
  3             NO_INDEX(SALES SALES_CHANNEL_BIX) */
  4   count(*) from SALES
  5  where CUST_ID    = 25939
  6  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

索引SALES_CUST_BIXのみを使用する実行計画となった。

索引SALES_CUST_BIXを可視に変更する

今まで不可視だった索引SALES_CUST_BIXを可視に変更して問合せを実行してみる。
この状態では索引SALES_CHANNEL_BIXのみが使用不可である。

SQL> alter index SALES_CUST_BIX visible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

1つ前と同じ実行計画となっていることがわかる。

まとめ

  • USE_INVISIBLE_INDEXESヒントを指定するとSQL文単位で使える不可視索引が全てCBOの評価対象となるので、INDEXヒントで使用したい索引を特定しようとしても結果として無視される。
  • 複数の不可視索引を作成し順番にテストするような場合、使いたくない索引をNO_INDEXヒントで指定しないと意図したテストとならない可能性があるので注意が必要である。

これらは、マニュアルにもMy Oracle Supportにも記述されていなかったので、不可視索引を使いこなす場合に覚えておきたい事実である。

今回はここまで