サブクエリーのアクセス順指定

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序(シーケンス)生成コマンド取得
・データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
→サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

メインクエリーのアクセス順はLEADINGやORDEREDヒントを組み込めば オプティマイザにテーブルアクセス順序を指定させることが可能ですが、サブクエリーを含めたアクセス順はなかなか特殊。 Oracleにはサブクエリーに対して名前を付けることができる(QB_NAME)ので、それを利用し、グローバルヒントで 一括指定してやればサブクエリーも含めたアクセス順序をある程度指定できるようになるみたいです。

参考例1

------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2016.06.07 作成
------------------------------------------------------------------
  select /*+ LEADING(PGML TOPL@ANTI_TOP STRL@ANTI_STR STPL)
             NL_AJ(@ANTI_TOP) INDEX(STRL@ANTI_TOP TEST_PGM_TOP_ID1)
             NL_AJ(@ANTI_STR) INDEX(STRL@ANTI_STR TEST_PGM_STRUCTURE_ID1)
             USE_NL(STPL)     INDEX(STPL TEST_PGM_STEP_ID1)
          */
         PGML.PGM_ID
       , PGML.PGM_NAME
       , STPL.VALID_STEP
       , STPL.COMNT_STEP
       , STPL.TOTAL_STEP
    from TEST_PGM_LIST PGML
       , TEST_PGM_STEP STPL
   where PGML.PGM_ID = STPL.PGM_ID(+)
     and not exists(
  select /*+ QB_NAME(ANTI_STR) */ *
    from TEST_PGM_STRUCTURE STRL
   where STRL.PGM_ID = PGML.PGM_ID
       )
     and not exists(
  select /*+ QB_NAME(ANTI_TOP) */ *
    from TEST_PGM_TOP TOPL
   where TOPL.PGM_ID = PGML.PGM_ID
       )
order by PGML.PGM_ID ASC
;

実行計画

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        | 11302 |   960K|       |  3256K  (1)| 00:02:08 |
|   1 |  SORT ORDER BY                        |                        | 11302 |   960K|  1128K|  3256K  (1)| 00:02:08 |
|   2 |   NESTED LOOPS OUTER                  |                        | 11302 |   960K|       |  3256K  (1)| 00:02:08 |
|   3 |    NESTED LOOPS ANTI                  |                        | 11302 |   739K|       |  3244K  (1)| 00:02:07 |
|   4 |     NESTED LOOPS ANTI                 |                        |  1130K|    64M|       |   982K  (3)| 00:00:39 |
|   5 |      TABLE ACCESS FULL                | TEST_PGM_LIST          |  1175K|    58M|       |  3382   (1)| 00:00:01 |
|*  6 |      INDEX FAST FULL SCAN             | TEST_PGM_TOP_ID1       |    41 |   328 |       |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN                  | TEST_PGM_STRUCTURE_ID1 |  1271K|  8692K|       |     2   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST_PGM_STEP          |     1 |    20 |       |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN                  | TEST_PGM_STEP_ID1      |     1 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("TOPL"."PGM_ID"="PGML"."PGM_ID")
   7 - access("STRL"."PGM_ID"="PGML"."PGM_ID")
   9 - access("PGML"."PGM_ID"="STPL"."PGM_ID"(+))

参考例2

------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2016.06.07 作成
------------------------------------------------------------------
  select /*+ LEADING(PGML STRL@ANTI_STR TOPL@ANTI_TOP STPL)
             NL_AJ(@ANTI_STR) INDEX(STRL@ANTI_STR TEST_PGM_STRUCTURE_ID1)
             NL_AJ(@ANTI_TOP) INDEX(STRL@ANTI_TOP TEST_PGM_TOP_ID1)
             USE_NL(STPL)     INDEX(STPL TEST_PGM_STEP_ID1)
          */
         PGML.PGM_ID
       , PGML.PGM_NAME
       , STPL.VALID_STEP
       , STPL.COMNT_STEP
       , STPL.TOTAL_STEP
    from TEST_PGM_LIST PGML
       , TEST_PGM_STEP STPL
   where PGML.PGM_ID = STPL.PGM_ID(+)
     and not exists(
  select /*+ QB_NAME(ANTI_STR) */ *
    from TEST_PGM_STRUCTURE STRL
   where STRL.PGM_ID = PGML.PGM_ID
       )
     and not exists(
  select /*+ QB_NAME(ANTI_TOP) */ *
    from TEST_PGM_TOP TOPL
   where TOPL.PGM_ID = PGML.PGM_ID
       )
order by PGML.PGM_ID ASC
;

実行計画

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        | 11679 |   992K|       |  2379K  (1)| 00:01:33 |
|   1 |  SORT ORDER BY                        |                        | 11679 |   992K|  1168K|  2379K  (1)| 00:01:33 |
|   2 |   NESTED LOOPS OUTER                  |                        | 11679 |   992K|       |  2378K  (1)| 00:01:33 |
|   3 |    NESTED LOOPS ANTI                  |                        | 11679 |   764K|       |  2366K  (1)| 00:01:33 |
|   4 |     NESTED LOOPS ANTI                 |                        | 11757 |   677K|       |  2356K  (1)| 00:01:33 |
|   5 |      TABLE ACCESS FULL                | TEST_PGM_LIST          |  1175K|    58M|       |  3382   (1)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                 | TEST_PGM_STRUCTURE_ID1 |  1264K|  8645K|       |     2   (0)| 00:00:01 |
|*  7 |     INDEX FAST FULL SCAN              | TEST_PGM_TOP_ID1       |     7 |    56 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST_PGM_STEP          |     1 |    20 |       |     2   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN                  | TEST_PGM_STEP_ID1      |     1 |       |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("STRL"."PGM_ID"="PGML"."PGM_ID")
   7 - filter("TOPL"."PGM_ID"="PGML"."PGM_ID")
   9 - access("PGML"."PGM_ID"="STPL"."PGM_ID"(+))

違いわかりました?


Copyright(c) 2014-2017 pakkin. All Rights Reserved.