CBO」タグアーカイブ

実行統計による実践的SQLチューニング(その2)

実行計画を実行順に表示させる

前回の投稿では、DBMS_XPLANパッケージのDISPLAY_CURSOR関数により実行統計を併記した実行計画の表示要領を紹介した。

しかし、実行計画ツリーからどのステップが起点となりどの順番で実行されるかを読み取るのはある程度の経験が必要であり、前回紹介した程度の行数であればともかく、数百ステップにもなる場合はベテランでも投げ出したくなる。

筆者は以前から実行計画ツリーを実行順に表示させることに関して試行錯誤を繰り返してきたが、この度方法を確立するに至ったので紹介したいと思う。

実行順表示スクリプト

DBMS_XPLAN.DISPLAY_COURSORの入力ソースはV$SQL_PLAN_STATISTICS_ALLビューであるので、このビューを使って情報を取得する。

前回投稿の中で aplan.sql スクリプトから呼ばれていた aplans.sql の内容が以下となる。

set lines 1000
col ID for 9999
col Operation for a60
col Name for a20
col Pstart for a13
col Pstop for a13
col A-Time for 9,990.00
col A-Rows for 999,999,999,990
col E-Rows for 999,999,999,990
col Starts for 999,999,999,990
-- 実行順実行統計出力
select
 ID
,"Operation"
,"Name"
,"Starts"
,"E-Rows"
,"A-Rows"
,"A-Time"
,"Buffers"
,"Reads"
,"Writes"
,"Srch Cols"
,"Pstart"
,"Pstop"
,"PartID"
from
(
  select
   rownum NO
  ,ID
  ,lpad(' ',DEPTH) || OPERATION ||' '|| OPTIONS "Operation"
  ,OBJECT_NAME "Name"
  ,LAST_STARTS "Starts"
  ,nvl(CARDINALITY,1) * LAST_STARTS "E-Rows" -- 1回の操作で処理される見積行数 * 見積処理回数 = 見積処理行数
  ,LAST_OUTPUT_ROWS "A-Rows"                 -- 実際の処理行数
  ,LAST_ELAPSED_TIME/1000000 "A-Time"
  ,LAST_CR_BUFFER_GETS "Buffers"
  ,LAST_DISK_READS "Reads"
  ,LAST_DISK_WRITES "Writes"
  ,SEARCH_COLUMNS "Srch Cols"
  --,COST
  ,PARTITION_START "Pstart"
  ,PARTITION_STOP "Pstop"
  ,PARTITION_ID "PartID"
  from
  (
    select a.* from 
     V$SQL_PLAN_STATISTICS_ALL a
    where a.SQL_ID    = '&1'
    and   a.TIMESTAMP = (select max(b.TIMESTAMP) from V$SQL_PLAN_STATISTICS_ALL b where b.SQL_ID = a.SQL_ID)
  )
  start with PARENT_ID is null
  connect by prior ID = PARENT_ID
  order siblings by ID desc
)
order by NO desc
;

解説

  • 49行目のV$SQL_PLAN_STATISTICS_ALLが実行計画情報の取得元となり、50行目のWHERE条件で表示対象のSQL_IDで絞っている。(階層問い合わせでWHERE句を指定してもstart with~connect byの後に評価されるので、このビュー全件が表示対象となり非常に高負荷な問い合わせとなってしまう。)
  • 共有プールをフラッシュせずにこのスクリプトを実行させた場合、1つのSQL_IDに対して2つ以上のPLAN_HASH_VALUEが取得される場合がある。その際実行計画が正しく表示されない可能性があるので、51行目で直近のTIMESTAMPのものだけ1つを表示対象としている。
  • 53〜54行目は階層問い合わせによって、次のIdがNullとなるId=0を起点として実行順にId値をたどる。
  • 55行目のsiblings句により同じ階層(DEPTH)のId値を並び替えているが、desc[endant]を指定することでId値は逆実行順に並ぶ。ちなみに「siblings」とは「きょうだい」を意味する。
  • 35行目はNested Loops Joinにおいて実際の行数(A-Rows)と比較しやすいように見積もり行数(E-Rows)を加工している。(参考: 津島博士のパフォーマンス講座 第68回 TEMP領域の続きとA-Rowsについて
  • 29〜55行目の問い合わせにおいて、30行目のROWNUM疑似列で順序番号(NO列)を取得しているが、Id=0を先頭とした逆実行順の検索結果をNO列の降順に並び替えることで、実行順に表示させている。(当初はこの部分がなく下から順にたどっていく表示にしていたが、わかりやすさに欠けていたので改良した。)

表示結果

実行順実行統計出力スクリプトによって表示させた結果が以下である。

前回投稿の最後でこのSQLにおけるステップの実行順をまとめたが、以下の結果のID列の順序と一致していことを確認してほしい。

実行計画のステップがどんなに多くても、このスクリプトを使えば実行順に表示させることができる。

ID Operation                                         Name           Starts E-Rows A-Rows A-Time Buffers  Reads Writes  Srch Cols Pstart  Pstop PartID
-- ------------------------------------------------- -------------- ------ ------ ------ ------ ------- ------ ------ ---------- ------- ----- ------
 8         INDEX SKIP SCAN                           I_TABLE001_2        1 38,050  3,060   2.96    3619   1917      0          2
 7        TABLE ACCESS BY INDEX ROWID BATCHED        TABLE_001           1 38,046  3,060   3.12    3706   1977      0          0
11          INDEX RANGE SCAN                         I_TABLE004_8    3,060  3,060      1 204.17  117860 114690      0          5 KEY     KEY        9
10         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE_004       3,060  3,060      1 204.19  117861 114691      0          0 KEY     KEY        9
 9        PARTITION RANGE ITERATOR                                   3,060  3,060      1 204.20  117861 114691      0          0 KEY     KEY        9
 6       NESTED LOOPS                                                    1      1      1 207.33  121567 116668      0          0
12       INDEX RANGE SCAN                            I_TABLE002PK        1      1      0   0.00       1      1      0          2
 5      NESTED LOOPS OUTER                                               1      1      1 207.34  121568 116669      0          0
 4     FILTER                                                            1      1      1 207.34  121568 116669      0          0
 3    FILTER                                                             1      1      1 207.34  121568 116669      0          0
 2   COUNT STOPKEY                                                       1      1      1 207.34  121568 116669      0          0
 1  SORT AGGREGATE                                                       1      1      1 207.34  121568 116669      0          0
 0 SELECT STATEMENT                                                      1      1      1 207.34  121568 116669      0          0

13行が選択されました。

経過: 00:00:00.02

A-Time列を上から順にたどっていき、値が急激に増えている箇所がボトルネックである。
この例ではId=11の「INDEX RANGE SCAN」がそれにあたる。

次回は、この結果から実際にどのようにチューニングを行なっていくかを追ってみる。

(続く)

実行統計による実践的SQLチューニング(その1)

この投稿はJPOUG in 15 minutes #8で発表した内容に加筆・整理したものです。

実行統計とは?

実行統計とは、DBMS_XPLANパッケージのDISPLAY_CURSOR関数における機能拡張で、SQL実行時に実行計画の各ステップ毎に出力行数や実行時間などの統計情報を取得し、実行後(正常終了および強制終了)に実行計画と共に統計情報を併記するものである。

ちなみに、本機能はOracle10g R2以降で使用可能となっている。

実行統計については以下の記事がよくまとまっている。
Oracle DatabaseでSQLの性能計測2(DBMS_XPLAN&DBMS_SQLTUNE編)【Oracle Database or GoldenGate Advent Calendar 2018 Day 8】

Oracle® Database SQLチューニング・ガイド 12c リリース1 (12.1) には以下の記述がある。
V$SQL_PLANビューを使用した計画の評価のガイドライン
ポイントをまとめると以下の2点となる。

  • 出力行数や経過時間など、計画に含まれる操作ごとに実際の実行統計を出力する。
  • 出力行数を除き、すべての統計は累積される。例えば結合操作の統計には、2つの入力の統計も含まれる。

実行統計が使えない時は、SQL文の性能は全体の経過時間と各ステップごとのコスト値で評価するしかなかった。
コスト値はリンクにあるように、性能を評価する絶対的な指標ではなく、実行時間と相関するものではない。
別の言い方をすると、I/OコストとCPUコストで見積もられる「コスト」を最小にするような実行計画を立案するのがコスト・ベース・オプティマイザ(CBO)であるが、コストの大小が必ずしも実行時間の長短でないことがSQLチューニングを難しくしているというのが、実行統計が実装される以前の課題であった。

一方、実行計画の各ステップごとに経過(累積)時間を表示させることができる実行統計により、SQLの中でボトルネックがどこに存在するかを的確に把握することができるので、以前のような「試行錯誤」的チューニングと比べ、より効率的なチューニングが可能となった。

SQL単性能試験の実際

それでは、SQL単性能試験をイメージして実践的なSQLチューニングの実際を考えてみよう。
SQL単性能試験とは、SQL*PlusからSQL文を単体で実行する試験であり、設定した性能目標(レスポンス、スループット)を達成するまでチューニングを行うものである。

アプリケーションが発行しうるすべてのSQL文を予め単体で実行し、性能上の問題点を完全に解決した上で、次の段階(総合試験等)に進むべきである。
カットオーバー直前で致命的な性能問題が発生することのないよう、十分なSQL単体試験を実施することは円滑なプロジェクト遂行にとって重要である。

考慮すべき点

意味のあるSQL単体試験を実施するために考慮すべき点を以下に挙げる。

1. 本番相当データ

SQL単性能試験を行う上で最も重要なのは、量および質で本番と同等のデータを使用することである。量とは将来の増加量を見越した十分なサイズ、質とは現実的な内容(値の分布等)のデータを準備することである。

セキュリティ面から本番データをそのまま試験で使うことは許されないことが多いが、本番データとあまりにもかけ離れたデータを使っては性能試験の妥当性を担保できない。

2. キャッシュ・クリア

SQL単性能試験を行う際、キャッシュをクリア(フラッシュ)した状態で実行時間を計測する。
キャッシュとはDBバッファおよび共有プールである。

キャッシュをクリアした状態でそのSQLの本当の実力を把握することができる。

データがDBバッファ上にあるとボトルネックの検出が困難になる。またパーティション数が非常に多い環境ではParseに要する時間が想定以上に長くなることが多く、共有プールをフラッシュすることでその状況を確認することができる。

3. 占有サーバ

性能測定をするサーバでは極力他の負荷がかかっていない状態であることが望ましく、無風状態で測定できるようサーバを占有できる環境が理想である。

著者が経験したあるプロジェクトでは、開発と性能測定を同じサーバで行わざるを得なかったため、測定結果が負荷により毎回変わってしまい客観的な判断ができない場合があった。

STATISTICS_LEVELパラメータを「ALL」に設定

実行統計を取得するために3つの方法がある

  1. STATISTICS_LEVELパラメータをALLに設定する
  2. SQL文にGATHER_PLAN_STATISTICSヒントを指定し実行する
  3. SQLトレースを有効にしてSQL文を実行する

実際にはSQL*Plusでログインしたセッション単位

alter session set STATISTICS_LEVEL=all;

とするのがよいだろう。

「alter system 〜」によりインスタンス・レベルで設定することも可能だが、実行される全てのSQLの実行統計が取得されSYSAUX表領域が枯渇する可能性があるのでお勧めしない。

キャッシュ・クリア

キャッシュ・クリア(フラッシュ)は以下のスクリプトをSQL実行前に実行することで行う。

pro *** FLUSH SHARED POOL ***
alter system flush shared_pool;
pro *** FLUSH BUFFER CACHE ***
alter system flush buffer_cache;
SQL> @flusys
*** FLUSH SHARED POOL ***
システムが変更されました。

*** FLUSH BUFFER CACHE ***
システムが変更されました。

SQL文の実行と経過時間の確認

それでは、実際にSQLを実行して結果を確認してみよう。

以下のSQLは、実際の業務で実行されたSQLをベースにテーブル名等を書き換えたサンプルSQLである。
コメントにあるように、オンラインSQLとして実行されているが、3分27秒もかかっておりチューニングが必要である。

SELECT /*+ ONLINE_SQL04S
           INDEX(T004 I_TABLE004_8) INDEX(T001 I_TABLE001_2)
           USE_NL(T002)
           LEADING(T001 T004 T002) */
 COUNT(*) AS COUNTNUM
FROM
 TABLE_004 T004
  INNER JOIN
  TABLE_001 T001
  ON  (T004.COL3091 = T001.COL3091
  AND  T004.COLA269 = T001.COLA269)
  LEFT OUTER JOIN
  TABLE_002 T002
  ON  (T002.COLA215 = T001.COLA215
  AND  T002.COL3091 = T004.COL3091)
WHERE
..... 以下省略 ..........
  COUNTNUM
----------
         1

経過: 00:03:27.35

SQL_IDの確認

SQLを実行した後、以下のスクリプトでSQL_IDを確認する。

コメントに記述した文字列を引数として実行する。

SET AUTOT OFF
SET COLSEP ' ' VERIFY OFF LINESIZE 140
COLUMN SQL_TEXT FOR A80
COLUMN SQL_ID FOR A13
COLUMN EXECUTIONS FOR '9999999'
COLUMN ELAPSED_TIME FOR '999999999999'
COLUMN LA_DATE FOR A10
COLUMN LA_TIME FOR A8
SELECT /* THISSQL */
    SUBSTR(SQL_TEXT, 1, 60) SQL_TEXT
  , SQL_ID
  , EXECUTIONS
  , ELAPSED_TIME
  , TO_CHAR(LAST_ACTIVE_TIME, 'YYYY/MM/DD') LA_DATE
  , TO_CHAR(LAST_ACTIVE_TIME, 'HH24:MI:SS') LA_TIME
FROM
  V$SQL
WHERE
  SQL_TEXT LIKE '%&1.%' AND NOT SQL_TEXT LIKE '%THISSQL%'
ORDER BY
  LAST_ACTIVE_TIME ASC
;
SQL> @vsql ONLINE_SQL04S

実行統計を併記した実行計画の表示

上で確認したSQL_IDを使用して、実行統計を併記した実行計画を表示させる。

9行目のDBMS_XPLAN.DISPLAY_CURSORと引数の設定がポイントである。

また、11行目は実行順に実行計画を表示させるスクリプトを呼び出している。(次回解説)

define SQLID=&1
set autot off
set trim on
set pages 10000
set lines 1000
set long 1000000
set longchunksize 1000000
set heading off
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQLID',null,'ALLSTATS LAST'));
set heading on
@aplans &SQLID  --実行順実行計画の表示
set lines 80

表示結果(横スクロールあり)

SQL> @aplan bvrwck53tfgkt

SQL_ID  bvrwck53tfgkt, child number 0
-------------------------------------
SELECT /*+ ONLINE_SQL04S INDEX(T004 I_TABLE004_8) INDEX(T001
I_TABLE001_2) USE_NL(T002) LEADING(T001 T004 T002) */     COUNT(*) AS
COUNTNUM FROM     TABLE_004 T004      INNER JOIN TABLE_001
T001 ON  ( T004.COL3091 = T001.COL3091
                   AND  T004.COLA269 = T001.COLA269 ) 
 LEFT OUTER JOIN TABLE_002 T002 ON  (
T002.COLA215 = T001.COLA215                                         AND
 T002.COL3091 = T004.COL3091 )  WHERE
T001.COLAH15 = '0'      AND   T004.COLAH15 = '0' 
 AND   T001.COLA215 =:B1       AND
(         T001.COLA293 = '2'          OR    (
T001.COLA293 = '1'              AND   T004.COL0157 <> 'B'
         )     )      AND   (
T001.COLA367

Plan hash value: 239732999

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|   1 |  SORT AGGREGATE                                  |              |      1 |      1 |      1 |00:03:27.34 |     121K|    116K|
|*  2 |   COUNT STOPKEY                                  |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|*  3 |    FILTER                                        |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|*  4 |     FILTER                                       |              |      1 |        |      1 |00:03:27.34 |     121K|    116K|
|   5 |      NESTED LOOPS OUTER                          |              |      1 |      1 |      1 |00:03:27.34 |     121K|    116K|
|   6 |       NESTED LOOPS                               |              |      1 |      1 |      1 |00:03:27.33 |     121K|    116K|
|*  7 |        TABLE ACCESS BY INDEX ROWID BATCHED       | TABLE_001    |      1 |  38046 |   3060 |00:00:03.12 |    3706 |   1977 |
|*  8 |         INDEX SKIP SCAN                          | I_TABLE001_2 |      1 |  38050 |   3060 |00:00:02.96 |    3619 |   1917 |
|   9 |        PARTITION RANGE ITERATOR                  |              |   3060 |      1 |      1 |00:03:24.20 |     117K|    114K|
|* 10 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE_004    |   3060 |      1 |      1 |00:03:24.19 |     117K|    114K|
|* 11 |          INDEX RANGE SCAN                        | I_TABLE004_8 |   3060 |      1 |      1 |00:03:24.17 |     117K|    114K|
|* 12 |       INDEX RANGE SCAN                           | I_TABLE002PK |      1 |      1 |      0 |00:00:00.01 |       1 |      1 |
------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<=TO_NUMBER(:B6)) 3 - filter(:B5>=:B4)
   4 - filter(("T001"."COLA367"='0' OR ("T001"."COLA367"='1' AND INTERNAL_FUNCTION("T004"."COL0310") AND
              "T002"."COLA362"="T004"."COLA363" AND "T002"."COLA364"<=LPAD(NVL("T004"."COLA366",'000'),3,'0') AND "T002"."COLA365">=LPAD(NVL("T004"."COLA366",'000'),3,'0'))))
   7 - filter(("T001"."COLA389"='0' OR "T001"."COLA389"='1' OR "T001"."COLA389"='2'))
   8 - access("T001"."COLA215"=:B1 AND "T001"."COLAH15"='0')
       filter(("T001"."COLA215"=:B1 AND INTERNAL_FUNCTION("T001"."COLA332") AND "T001"."COLAH15"='0'))
  10 - filter((DECODE("T001"."COLA389",'2',NVL("T004"."COLA526",'19000101'),'19000101')<NVL("T001"."COL3277",'21001231')
              AND (INTERNAL_FUNCTION("T001"."COLA389") OR ("T001"."COLA389"='2' AND INTERNAL_FUNCTION("T004"."COLA415"))) AND
              ("T001"."COLA293"='2' OR ("T001"."COLA293"='1' AND "T004"."COL0157"<>'B'))))
  11 - access("T004"."COLA269"="T001"."COLA269" AND "T004"."COL3091"="T001"."COL3091" AND "T004"."COL0017">=:B4 AND
              "T004"."COLA318"=:B3 AND "T004"."COLAH15"='0' AND "T004"."COL0017"<=:B5) filter(("T004"."COLA318"=:B3 AND DECODE("T001"."COLA389",'2',"T004"."COL0017",'21001231')>=:B2 AND
               "T004"."COLAH15"='0' AND INTERNAL_FUNCTION("T004"."COLA415")))
  12 - access("T002"."COLA215"=:B1 AND "T002"."COL3091"="T004"."COL3091")


57行が選択されました。

経過: 00:00:00.39

項目説明

Starts : そのステップが実行された回数
E-Rows : CBOが見積もった(1回あたりの)処理行数
A-Rows : そのステップでの処理行数
A-Time : (累積)実行時間
Buffers : バッファ・アクセス数
Reads : ディスクから読み込まれたブロック数

ステップの実行順

実行計画ツリーの見方は「右から左、上から下」が基本である。

上の実行計画では、インデントの一番深いId=11が一番最初に実行されるように思ってしまうが、実際はId=6「NESTED LOOPS」の最初の入力側(駆動表又は外部表)となるId=8「INDEX SKIP SCAN」が一番最初に実行される。

実行順をまとめると

8 → 7 → 11 → 10 → 9 → 6 → 12 → 5 → 4 → 3 → 2 → 1 → 0

となり、Id=0のA-Time 3:27.34 がこのSQLの実行時間となる。(SQL*Plusのtiming表示の経過時間と若干異なることに注意)

実行統計を併記するようにしても実行順を間違えるとボトルネックの判断を間違えてしまう可能性がある。
ということで、次回は実行計画を実行順に表示させる方法を紹介する。

今回はここまで

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にも記述されていなかったので、不可視索引を使いこなす場合に覚えておきたい事実である。

今回はここまで