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

いきさつ

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

参考例１

------------------------------------------------------------------
-- 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"(+))

参考例２

------------------------------------------------------------------
-- 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"(+))

違いわかりました？