#まえがき

チューニングできるようになりたいなーとおもい、自分なりに方法を考えていたところ、本記事になりました。ヒント句でsqlの実行計画を変えて、オペレーションを調べていくことで実行計画に慣れ親しむことができるのではないか。慣れ親しんだら、「このsqlはこうすればいいかんじになるのでは？？」みたいな代替案が浮かびやすくなるのではないかという、気持ちではじめました。「このヒント句与えたら、このsqlの実行計画どうなるのか」「こんな感じのsql書いたら、実行計画どうなるんだろ」みたいな要素が多いです。ヒント句の多くはANSI準拠の結合文を使用したsqlでは効果がみられないこと多いので、本記事のsqlはOracle独自の結合文で書いていきます。

#ヒント句

取り上げるヒント句は「これなんだろー」みたいな感覚でチョイスしていったので、体系的にはまとめられていません。あそびなので、興味の赴くままに選んでいました。ヒントなしパターンとヒントありパターンで確認していました。対象のsqlは参考文献とかを参考にしながら、思いつくままに書いたものです。対象のsql実行後の実行計画とかオブジェクト別名、絞込み条件は以下の**#1.**で確認していました。対象のsqlごとにパターン比較したあと、コメントを書いていました。qb_nameヒントは分かりやすくするため、どちらのパターンでもつけています。

**#1.**別名がわかるやつ

sql（開く）

SELECT
	*
FROM
	TABLE ( dbms_xplan.display_cursor(format => 'ALIAS PREDICATE') );

**#2.**sqlid取得

sql（開く）

SELECT
	TO_CHAR(to_timestamp(last_load_time DEFAULT NULL ON CONVERSION ERROR, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') AS last_load_time
	, sql_id
	, sql_fulltext
	, service
	, module
FROM
	v$sql
WHERE
	regexp_replace(sql_fulltext, '[[:space:]]', '', 1, 0, 'i') LIKE '%'
	                                                                   || 'MONITER'
	                                                                   || '%'
ORDER BY
	1 DESC;

**#3.**実測と予測の差が分かるやつ

sql（開く）

ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT
	*
FROM
	TABLE ( dbms_xplan.display_cursor('sqlid', NULL, 'ALL ALLSTATS LAST ADAPTIVE') );

**#4.**統計情報の取得に使用するplsqlを作成するsql

sql（開く）

SET TIMING ON

SELECT
	'exec DBMS_STATS.GATHER_TABLE_STATS(ownname => '
	|| ''''
	|| (
		SELECT
			username
		FROM
			user_users
	)
	|| ''''
	|| ', tabname => '
	|| ''''
	|| table_name
	|| ''''
	|| ', estimate_percent => '
	|| 100
	|| ',CASCADE=>TRUE);' AS build_sql
FROM
	user_tables;

**#5.**統計情報の更新確認

sql（開く）

SELECT
	TO_CHAR(last_analyzed, 'MM/DD HH24:MI:SS') AS last_analyzed
	, table_name
	, column_name
	, num_distinct
	, sample_size
FROM
	user_tab_col_statistics;

SELECT
	TO_CHAR(last_analyzed, 'MM/DD HH24:MI:SS') AS last_analyzed
	, table_name
	, num_rows
	, sample_size
FROM
	user_tab_statistics;

SELECT
	TO_CHAR(last_analyzed, 'MM/DD HH24:MI:SS') AS last_analyzed
	, index_name
	, table_name
	, blevel
	, leaf_blocks
	, distinct_keys
	, num_rows
	, sample_size
FROM
	user_ind_statistics;

SELECT
	TO_CHAR(last_analyzed, 'MM/DD HH24:MI:SS') AS last_analyzed
	, table_name
	, composite
	, partition_name
	, subpartition_count
	, high_value
	, partition_position
	, num_rows
	, sample_size
FROM
	user_tab_partitions;

SELECT
	TO_CHAR(last_analyzed, 'MM/DD HH24:MI:SS') AS last_analyzed
	, index_name
	, composite
	, partition_name
	, subpartition_count
	, high_value
	, partition_position
	, status
	, blevel
	, leaf_blocks
	, distinct_keys
	, num_rows
	, sample_size
FROM
	user_ind_partitions;

##GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS

ヒントなしでNO_GATHER_OPTIMIZER_STATISTICSをヒントありでGATHER_OPTIMIZER_STATISTICSを試したいと思う。さっそくルール破った。

###サンプル①

####ヒントなし

sql（開く）

DROP TABLE calendar PURGE;
CREATE TABLE calendar AS
WITH sub AS (
SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */
    to_char(ind,'cc') AS century
    ,to_char(ind,'yyyymmdd') AS yyyymmdd
    ,to_char(ind,'yyyy') AS yyyy
    ,to_char(ind,'mm') AS mm
    ,to_char(ind,'dd') AS dd
    ,to_char(ind,'q') AS quater
    ,to_char(ind,'mon') AS japan_mon
    ,to_char(ind,'rm') AS roman_mon
    ,to_char(ind,'ddd') AS day_per_year
    ,to_char(ind,'ww') AS week_per_year
    ,to_char(ind,'w') AS week_per_month
    ,to_char(ind,'d') AS day_of_week
    ,to_char(ind,'dy') AS jan_day_of_week
    ,to_char(ind,'day') AS jan_day_of_week_rich
FROM
    dual
MODEL
RETURN UPDATED ROWS
DIMENSION BY(sysdate AS ind)
MEASURES(
        0 AS val
        )
RULES(
        val[FOR ind FROM TO_DATE('2018-01-01') TO TO_DATE('2018-12-31') INCREMENT INTERVAL '1' DAY] = 0
        )
)
SELECT * FROM sub;

実行計画（開く）

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |          |       |       |     4 (100)|          |
|   1 |  LOAD AS SELECT        | CALENDAR |       |       |            |          |
|   2 |   VIEW                 |          |     1 |    53 |     3   (0)| 00:00:01 |
|   3 |    SQL MODEL ORDERED   |          |     1 |       |            |          |
|   4 |     FAST DUAL          |          |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

コメント

NO_GATHER_OPTIMIZER_STATISTICSだからバルクロード系のDDLであるCREATE TABLE ... AS SELECTやINSERT INTO ... SELECT（direct-path insert）を実行しても統計情報は取得されないようになっているのではと思い、統計情報を格納しているテーブルを確認した。確かになかった。

####ヒントあり

sql（開く）

DROP TABLE calendar PURGE;
CREATE TABLE calendar AS
WITH sub AS (
SELECT /*+ GATHER_OPTIMIZER_STATISTICS */
    to_char(ind,'cc') AS century
    ,to_char(ind,'yyyymmdd') AS yyyymmdd
    ,to_char(ind,'yyyy') AS yyyy
    ,to_char(ind,'mm') AS mm
    ,to_char(ind,'dd') AS dd
    ,to_char(ind,'q') AS quater
    ,to_char(ind,'mon') AS japan_mon
    ,to_char(ind,'rm') AS roman_mon
    ,to_char(ind,'ddd') AS day_per_year
    ,to_char(ind,'ww') AS week_per_year
    ,to_char(ind,'w') AS week_per_month
    ,to_char(ind,'d') AS day_of_week
    ,to_char(ind,'dy') AS jan_day_of_week
    ,to_char(ind,'day') AS jan_day_of_week_rich
FROM
    dual
MODEL
RETURN UPDATED ROWS
DIMENSION BY(sysdate AS ind)
MEASURES(
        0 AS val
        )
RULES(
        val[FOR ind FROM TO_DATE('2018-01-01') TO TO_DATE('2018-12-31') INCREMENT INTERVAL '1' DAY] = 0
        )
)
SELECT * FROM sub;

実行計画（開く）

---------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |          |       |       |     4 (100)|          |
|   1 |  LOAD AS SELECT                  | CALENDAR |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |     1 |    53 |     3   (0)| 00:00:01 |
|   3 |    VIEW                          |          |     1 |    53 |     3   (0)| 00:00:01 |
|   4 |     SQL MODEL ORDERED            |          |     1 |       |            |          |
|   5 |      FAST DUAL                   |          |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$2
   3 - SEL$1 / SUB@SEL$2
   4 - SEL$1
   5 - SEL$1 / DUAL@SEL$1

コメント

実測データと予測データに乖離が出ないように、初回のテストデータ投入の際にはGATHER_OPTIMIZER_STATISTICSヒントつけておくと、DBMS_STATS.GATHER_TABLE_STATSファンクションの実行の手間が省けるのかも。。統計情報を格納しているテーブルを確認したところ、更新されていた。

##GATHER_PLAN_STATISTICS

以下のsqlを実行する代わりに、対象のsql内に組み込んで実行できる。

ALTER SESSION SET STATISTICS_LEVEL = ALL;

実行計画の統計情報を集められなかったとき、以下の結果が出力される。おおーとなった。

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

コメント

ヒント書きすぎるとよく分からなくなるから、セッション単位で上記のsqlで切り替えるのが楽でいいし、複雑なsqlだとどこにGATHER_PLAN_STATISTICSヒントかけばいいか分からなくなる。私は。

PUSH_PRED

述語のプッシュとよばれるヒント。マージされていないビュー（インラインビュー）に対して指定した述語を組み込むことで、使用できるインデックスの幅を広げてあげることができるようです。

###サンプル①

####ヒントなし

sql（開く）

WITH sub AS (
	SELECT /*+ qb_name(q1)*/
		s1.warehouse_code
		, s1.partition_key
		, s1.cust_id
		, s1.store_no
		, s1.item
		, COUNT(s1.seq) AS cnt
	FROM
		sales_trn s1
	WHERE
		s1.sales_date = '20181207'
	GROUP BY
		s1.warehouse_code
		, s1.partition_key
		, s1.cust_id
		, s1.store_no
		, s1.item
) SELECT /*+ qb_name(q2) */
	s1.*
	, s2.item_kbn
  FROM
	item_mst s2
	, sub s1
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.partition_key = s2.partition_key
	AND s1.item = s2.item
	AND s2.warehouse_code = 'w_BS98'
	AND s2.partition_key = 'w_0001';

実行計画（開く）

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |              |       |       |    13 (100)|          |       |       |
|   1 |  HASH GROUP BY                                |              |     3 |   237 |    13   (8)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                                |              |     3 |   237 |    12   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                               |              |     9 |   237 |    12   (0)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE                     |              |     1 |    35 |     2   (0)| 00:00:01 |     1 |     1 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST     |     1 |    35 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |       INDEX RANGE SCAN                        | ITEM_MST_PK  |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   7 |     PARTITION LIST SINGLE                     |              |     9 |       |     1   (0)| 00:00:01 |     1 |     1 |
|*  8 |      INDEX RANGE SCAN                         | SALES_TRN_PK |     9 |       |     1   (0)| 00:00:01 |     1 |     1 |
|*  9 |    TABLE ACCESS BY LOCAL INDEX ROWID          | SALES_TRN    |     3 |   132 |    10   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S2@Q2
   6 - SEL$E72608F0 / S2@Q2
   8 - SEL$E72608F0 / S1@Q1
   9 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("S2"."WAREHOUSE_CODE"='w_BS98' AND "S2"."PARTITION_KEY"='w_0001')
   8 - access("S1"."WAREHOUSE_CODE"='w_BS98' AND "S1"."SALES_DATE"='20181207' AND "S1"."PARTITION_KEY"='w_0001')
   9 - filter("S1"."ITEM"="S2"."ITEM")

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST

コメント

ITEM_MST_PKに対し、INDEX RANGE SCANし（id=5）、ローカルインデックスより取得できたrowidをもとにITEM_MSTへバッチアクセスする（id=6）。PARTITION LIST SINGLEと出ている列のPstart、Pstopが同じ値のため、パーティションプルーニングが行われている（id=4）。推移律を活かしSALES_TRN_PKのみにINDEX RANGE SCANし、紐付くrowidのみを取得する（id=8）。1回目のNLの結果、9行残る（id=3）。これが2回目のNLの駆動表のループ回数となる（id=2）。id=8で取得したrowidから、SALES_TRNにTABLE ACCESS BY LOCAL INDEX ROWIDでテーブルアクセスし、ITEM_MSTより取得した値の等値判定を実施（id=9）。2回目のNLの結果3行残る（id=2）。この結果からGROUP BYを行う（id=1）。1回目のNLではSALES_TRNまでアクセスするのではなく、SALES_TRN_PKのみにアクセス。2回目のNLでは1回目のNLの取得結果を駆動表とし、SALES_TRN（内部表）にバッチアクセスする。11gからこれをNested Loops join Batchingと呼んでいるらしい。

####ヒントあり

sql（開く）

WITH sub AS (
	SELECT /*+ qb_name(q1)*/
		s1.warehouse_code
		, s1.partition_key
		, s1.cust_id
		, s1.store_no
		, s1.item
		, COUNT(s1.seq) AS cnt
	FROM
		sales_trn s1
	WHERE
		s1.sales_date = '20181207'
	GROUP BY
		s1.warehouse_code
		, s1.partition_key
		, s1.cust_id
		, s1.store_no
		, s1.item
) SELECT /*+ qb_name(q2) push_pred(s1@q2) */
	s1.*
	, s2.item_kbn
  FROM
	item_mst s2
	, sub s1
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.partition_key = s2.partition_key
	AND s1.item = s2.item
	AND s2.warehouse_code = 'w_BS98'
	AND s2.partition_key = 'w_0001';

実行計画（開く）

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |              |       |       |     6 (100)|          |       |       |
|   1 |  NESTED LOOPS                                 |              |     7 |   770 |     6  (17)| 00:00:01 |       |       |
|   2 |   PARTITION LIST SINGLE                       |              |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  | ITEM_MST     |     1 |    45 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     INDEX RANGE SCAN                          | ITEM_MST_PK  |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   5 |   VIEW PUSHED PREDICATE                       |              |     1 |    65 |     4  (25)| 00:00:01 |       |       |
|   6 |    SORT GROUP BY                              |              |     1 |    76 |     4  (25)| 00:00:01 |       |       |
|   7 |     PARTITION LIST SINGLE                     |              |     1 |    76 |     3   (0)| 00:00:01 |     1 |     1 |
|*  8 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |     1 |    76 |     3   (0)| 00:00:01 |     1 |     1 |
|*  9 |       INDEX RANGE SCAN                        | SALES_TRN_PK |     1 |       |     2   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q2          
   3 - Q2           / S2@Q2
   4 - Q2           / S2@Q2
   5 - SEL$00A14AFC / S1@Q2
   6 - SEL$00A14AFC
   8 - SEL$00A14AFC / S1@Q1
   9 - SEL$00A14AFC / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S2"."WAREHOUSE_CODE"='w_BS98' AND "S2"."PARTITION_KEY"='w_0001')
   8 - filter("S1"."ITEM"="S2"."ITEM")
   9 - access("S1"."WAREHOUSE_CODE"='w_BS98' AND "S1"."SALES_DATE"='20181207' AND "S1"."PARTITION_KEY"='w_0001')

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST

コメント

VIEW PUSHED PREDICATEがid=5で現れている。NLが一回になった。costがヒントなしに比べて半分ぐらいまで減少した。ヒントなしのHASH GROUP BY（id=1）とヒントありのSORT GROUP BY（id=6）のコスト差によるものかな。group byを含むインラインビューはマージしないほうが低コストでよろしいみたいな感覚であろう。

###サンプル②

lateralで試してみた。結合してからサマル場合と比較してみた。もはやヒント関係ないけど。

####ヒントなし

結合してからサマル版。

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.warehouse_code
	, s1.partition_key
	, s1.cust_id
	, s1.store_no
	, s1.item
	, s2.item_kbn
	, COUNT(s1.seq) AS cnt
FROM
	item_mst s2
	, sales_trn s1
WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.partition_key = s2.partition_key
	AND s1.item = s2.item
	AND s2.warehouse_code = 'w_BS98'
	AND s2.partition_key = 'w_0001'
	AND s1.sales_date = '20181207'
GROUP BY
	s1.warehouse_code
	, s1.partition_key
	, s1.cust_id
	, s1.store_no
	, s1.item
	, s2.item_kbn;

実行計画（開く）

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |              |       |       |     8 (100)|          |       |       |
|   1 |  HASH GROUP BY                                  |              |    27 |  2160 |     8  (25)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                                  |              |    27 |  2160 |     7  (15)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                                 |              |    27 |  2160 |     7  (15)| 00:00:01 |       |       |
|   4 |     VIEW                                        | VW_GBF_7     |     1 |    36 |     3  (34)| 00:00:01 |       |       |
|   5 |      HASH GROUP BY                              |              |     1 |    23 |     3  (34)| 00:00:01 |       |       |
|   6 |       PARTITION LIST SINGLE                     |              |     1 |    23 |     2   (0)| 00:00:01 |     1 |     1 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST     |     1 |    23 |     2   (0)| 00:00:01 |     1 |     1 |
|*  8 |         INDEX RANGE SCAN                        | ITEM_MST_PK  |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   9 |     PARTITION LIST ITERATOR                     |              |     3 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 10 |      INDEX RANGE SCAN                           | SALES_TRN_PK |     3 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 11 |    TABLE ACCESS BY LOCAL INDEX ROWID            | SALES_TRN    |    27 |  1188 |     4   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$77E375BF
   4 - SEL$A603AE9E / VW_GBF_7@SEL$9412CAF2
   5 - SEL$A603AE9E
   7 - SEL$A603AE9E / S2@Q2
   8 - SEL$A603AE9E / S2@Q2
  10 - SEL$77E375BF / S1@Q2
  11 - SEL$77E375BF / S1@Q2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("S2"."WAREHOUSE_CODE"='w_BS98' AND "S2"."PARTITION_KEY"='w_0001')
  10 - access("S1"."WAREHOUSE_CODE"="ITEM_3" AND "S1"."SALES_DATE"='20181207' AND "S1"."PARTITION_KEY"="ITEM_2")
       filter("S1"."PARTITION_KEY"="ITEM_2")
  11 - filter("S1"."ITEM"="ITEM_1")

マッピング表

オブジェクト別名	テーブル名
S1@Q2	SALES_TRN
S2@Q2	ITEM_MST

コメント

HASH GROUP BYが2回出現（id=1とid=5）。NLが2回出現（id=2とid=3）。

####ヒントあり

インラインビューをlateralでCROSS APPLYして左相関させる版。

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.item_kbn
FROM
	item_mst s2
	, LATERAL (
		SELECT /*+ qb_name(q1)*/
			s1.warehouse_code
			, s1.partition_key
			, s1.cust_id
			, s1.store_no
			, s1.item
			, COUNT(s1.seq) AS cnt
		FROM
			sales_trn s1
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.partition_key = s2.partition_key
			AND s1.item = s2.item
			AND s1.sales_date = '20181207'
		GROUP BY
			s1.warehouse_code
			, s1.partition_key
			, s1.cust_id
			, s1.store_no
			, s1.item
	) s1
WHERE
	s2.warehouse_code = 'w_BS98'
	AND s2.partition_key = 'w_0001';

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                 |       |       |     9 (100)|          |       |       |
|   1 |  NESTED LOOPS                                 |                 |     1 |   106 |     9  (12)| 00:00:01 |       |       |
|   2 |   PARTITION LIST SINGLE                       |                 |     1 |    23 |     2   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  | ITEM_MST        |     1 |    23 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     INDEX RANGE SCAN                          | ITEM_MST_PK     |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   5 |   VIEW                                        | VW_LAT_4A0C7615 |     1 |    83 |     7  (15)| 00:00:01 |       |       |
|   6 |    SORT GROUP BY                              |                 |     1 |    44 |     7  (15)| 00:00:01 |       |       |
|   7 |     PARTITION LIST ITERATOR                   |                 |     1 |    44 |     6   (0)| 00:00:01 |   KEY |   KEY |
|*  8 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN       |     1 |    44 |     6   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |       INDEX RANGE SCAN                        | SALES_TRN_PK    |     3 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q2
   3 - Q2 / S2@Q2
   4 - Q2 / S2@Q2
   5 - Q1 / S1@Q2
   6 - Q1
   8 - Q1 / S1@Q1
   9 - Q1 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S2"."WAREHOUSE_CODE"='w_BS98' AND "S2"."PARTITION_KEY"='w_0001')
   8 - filter("S1"."ITEM"="S2"."ITEM")
   9 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."SALES_DATE"='20181207' AND 
              "S1"."PARTITION_KEY"="S2"."PARTITION_KEY")
       filter("S1"."PARTITION_KEY"="S2"."PARTITION_KEY")

マッピング表

オブジェクト別名	テーブル名
S1@Q2	SALES_TRN
S2@Q2	ITEM_MST

コメント

SORT GROUP BYが1回出現（id=6）。NLが1回（id=1）。Viewが出てくるんだな。

##NO_INDEX MATERIALIZE INLINE

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	*
FROM
	sales_trn
WHERE
	seq = 1;

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |       |       |     7 (100)|          |       |       |
|   1 |  PARTITION LIST ALL                        |              |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|*  3 |    INDEX SKIP SCAN                         | SALES_TRN_PK |     1 |       |     6   (0)| 00:00:01 |     1 |     3 |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   2 - Q1 / SALES_TRN@Q1
   3 - Q1 / SALES_TRN@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("SEQ"=1)
       filter("SEQ"=1)

マッピング表

オブジェクト別名	テーブル名
SALES_TRN@Q1	SALES_TRN

コメント

テーブル別名つけないと、そのままテーブル名がオブジェクト別名になるんだ。パーティション絞ってないから、パーティションなめまわすことになり、PARTITION LIST ALL生まれたとおもう。

####ヒントあり

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

sql（開く）

SELECT /*+ qb_name(q1) no_index(sales_trn@q1 sales_trn_pk) */
	*
FROM
	sales_trn
WHERE
	seq = 1;

実行計画（開く）

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |   651 (100)|          |       |       |
|   1 |  PARTITION LIST ALL|           |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| SALES_TRN |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   2 - Q1 / SALES_TRN@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("SEQ"=1)

マッピング表

オブジェクト別名	テーブル名
SALES_TRN@Q1	SALES_TRN

コメント

SALES_TRN_PKに対するINDEX SKIP SCANがなくなった！

###サンプル②

####ヒントなし

sql（開く）

SELECT
	*
FROM
	(
		SELECT /*+ qb_name(q1) */
			*
		FROM
			sales_trn s1
		WHERE
			s1.seq = 1
	) sub;

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |       |       |     7 (100)|          |       |       |
|   1 |  PARTITION LIST ALL                        |              |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|*  3 |    INDEX SKIP SCAN                         | SALES_TRN_PK |     1 |       |     6   (0)| 00:00:01 |     1 |     3 |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - SEL$74528C06 / S1@Q1
   3 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("S1"."SEQ"=1)
       filter("S1"."SEQ"=1)

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN

コメント

インラインビューにもオブジェクト別名つけられるんだな。テーブル別名を記載すると、テーブル名ではなくてテーブル別名でオブジェクト別名作成されるんだな。

####ヒントあり

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

sql（開く）

SELECT /*+ no_index(s1@q1 sales_trn_pk) */
	*
FROM
	(
		SELECT /*+ qb_name(q1) */
			*
		FROM
			sales_trn s1
		WHERE
			s1.seq = 1
	) sub;

実行計画（開く）

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |   651 (100)|          |       |       |
|   1 |  PARTITION LIST ALL|           |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| SALES_TRN |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("S1"."SEQ"=1)

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN

コメント

メインクエリからインラインビューで指定したオブジェクト別名を使ってアクセスパスをコントロールできるんだ。

###サンプル③

####ヒントなし

sql（開く）

WITH sub AS (
	SELECT /*+ qb_name(q1) */
		*
	FROM
		sales_trn s1
	WHERE
		s1.seq = 1
) SELECT
	*
  FROM
	sub;

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |              |       |       |     7 (100)|          |       |       |
|   1 |  PARTITION LIST ALL                        |              |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |     1 |   128 |     7   (0)| 00:00:01 |     1 |     3 |
|*  3 |    INDEX SKIP SCAN                         | SALES_TRN_PK |     1 |       |     6   (0)| 00:00:01 |     1 |     3 |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - SEL$74528C06 / S1@Q1
   3 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("S1"."SEQ"=1)
       filter("S1"."SEQ"=1)

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN

コメント

with句でもオブジェクト別名は指定できる。

####ヒントあり

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

sql（開く）

WITH sub AS (
	SELECT /*+ qb_name(q1) */
		*
	FROM
		sales_trn s1
	WHERE
		s1.seq = 1
) SELECT /*+ no_index(s1@q1 sales_trn_pk) */
	*
  FROM
	sub;

実行計画（開く）

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |   651 (100)|          |       |       |
|   1 |  PARTITION LIST ALL|           |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| SALES_TRN |     1 |   128 |   651   (1)| 00:00:01 |     1 |     3 |
------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("S1"."SEQ"=1)

オブジェクト別名

表名（別名可）@問合せブロック名

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN

コメント

with句のメインクエリからでもオブジェクト別名を使ってアクセスパスをコントロールできる。

###サンプル④

####ヒントなし

sql（開く）

WITH sub1 AS (
	SELECT /*+ qb_name(q1) */
		s1.*
	FROM
		sales_trn s1
), sub2 AS (
	SELECT /*+ qb_name(q2) */
		s1.*
	FROM
		item_mst s1
), sub3 AS (
	SELECT /*+ qb_name(q3) */
		s1.*
	FROM
		cust_mst s1
) SELECT
	s1.*
	, s2.*
	, s3.*
  FROM
	sub1 s1
	, sub2 s2
	, sub3 s3
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.item = s2.item
	AND s1.cust_id = s3.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 |   118K|   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   291 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    72 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F35B1A08
   3 - SEL$F35B1A08 / S1@Q2
   4 - SEL$F35B1A08 / S1@Q2
   5 - SEL$F35B1A08 / S1@Q3
   7 - SEL$F35B1A08 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S1"."WAREHOUSE_CODE" AND "S1"."ITEM"="S1"."ITEM" AND 
              "S1"."CUST_ID"="S1"."CUST_ID")
   4 - access("S1"."WAREHOUSE_CODE"='w_C5MN' AND "S1"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

with句のqueryごとにオブジェクト別名は指定できる。パーティションひとつに絞ったから、PARTITION LIST SINGLEが生まれた。

####ヒントあり

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

sql（開く）

WITH sub1 AS (
	SELECT /*+ qb_name(q1) */
		s1.*
	FROM
		sales_trn s1
), sub2 AS (
	SELECT /*+ qb_name(q2) */
		s1.*
	FROM
		item_mst s1
), sub3 AS (
	SELECT /*+ qb_name(q3) */
		s1.*
	FROM
		cust_mst s1
) SELECT /*+ no_index(s1@q2 item_mst_pk) */
	s1.*
	, s2.*
	, s3.*
  FROM
	sub1 s1
	, sub2 s2
	, sub3 s3
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.item = s2.item
	AND s1.cust_id = s3.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |       |   225 (100)|          |       |       |
|*  1 |  HASH JOIN             |           |   180 | 40500 |   225   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS         |           |     1 |    97 |     6   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL   | ITEM_MST  |     1 |    72 |     3   (0)| 00:00:01 |       |       |
|   4 |    TABLE ACCESS FULL   | CUST_MST  |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   5 |   PARTITION LIST SINGLE|           |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  6 |    TABLE ACCESS FULL   | SALES_TRN |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$F35B1A08
   3 - SEL$F35B1A08 / S1@Q2
   4 - SEL$F35B1A08 / S1@Q3
   6 - SEL$F35B1A08 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S1"."WAREHOUSE_CODE" AND "S1"."ITEM"="S1"."ITEM" AND 
              "S1"."CUST_ID"="S1"."CUST_ID")
   3 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   6 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

ITEM_MST_PKに対するINDEX UNIQUE SCANがなくなった！コストも増えた。フィルタ(filter)述語はデータブロックにアクセスした後、取得データを除外する際に適用される。アクセス(access)述語はどのデータブロックにアクセスするかを絞り込むための条件。どちらも述語に合致したデータのみが得られるという点では同じ。

###サンプル⑤

####ヒントなし

sql（開く）

WITH sub1 AS (
	SELECT /*+ qb_name(q1) */
		s1.*
	FROM
		sales_trn s1
), sub2 AS (
	SELECT /*+ qb_name(q2) */
		s1.*
		, s2.*
	FROM
		item_mst s1
		, cust_mst s2
) SELECT
	s1.*
	, s2.*
  FROM
	sub1 s1
	, sub2 s2
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.item = s2.item
	AND s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 |   118K|   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   291 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    72 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$62BA2549
   3 - SEL$62BA2549 / S1@Q2
   4 - SEL$62BA2549 / S1@Q2
   5 - SEL$62BA2549 / S2@Q2
   7 - SEL$62BA2549 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S1"."WAREHOUSE_CODE" AND "S1"."ITEM"="S1"."ITEM" AND 
              "S1"."CUST_ID"="S2"."CUST_ID")
   4 - access("S1"."WAREHOUSE_CODE"='w_C5MN' AND "S1"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

ITEM_MST_PKに対するINDEX UNIQUE SCANはwhere句にitem_mstのPK指定したため。sqlで指定したのはSALES_TRNだけど推移律みたいな数学的な法則が働いて、ITEM_MSTにも同じ絞込みがされたことと認識されるらしい。ITEM_MST_PKにaccessして取得したrowidをもとにテーブルアクセスして内部表であるCUST_MSTにアクセス。CUST_MSTはwhere句に絞込み条件つけていないので、テーブルフルスキャン。NESTED LOOPSした結果、駆動表ITEM_MST1回ループにつき、内部表CUST_MSTフルスキャンで3行取得なので、1×3=3行取得できる。そのあと、結合列(id=1のaccess述語で分かる)にHASH関数がかまされ、PGA内にハッシュ表が作成される。SALES_TRNのfilter条件のふるいにかけられて取得できた540行の結合列(id=1のaccess述語で分かる)を順にHASH関数にかけ、先にPGAへ読みこんだハッシュ表の結合列と照合を実施し、結合条件に合致する行を特定する。

####ヒントあり

/*+ MATERIALIZE */

sql（開く）

```sql WITH sub1 AS ( SELECT /*+ qb_name(q1) */ s1.* FROM sales_trn s1 ), sub2 AS ( SELECT /*+ qb_name(q2) materialize */ s1.* , s2.* FROM item_mst s1 , cust_mst s2 ) SELECT s1.* , s2.* FROM sub1 s1 , sub2 s2 WHERE s1.warehouse_code = s2.warehouse_code AND s1.item = s2.item AND s1.cust_id = s2.cust_id AND s1.warehouse_code = 'w_C5MN' AND s1.item = 'p_0YL4' AND s1.partition_key = 'w_0003'; ```

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |   229 (100)|          |       |       |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67CF_10BEAC9 |       |       |            |          |       |       |
|   3 |    MERGE JOIN CARTESIAN                  |                            |   117 | 11349 |     8   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS FULL                    | CUST_MST                   |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   5 |     BUFFER SORT                          |                            |    39 |  2808 |     5   (0)| 00:00:01 |       |       |
|   6 |      TABLE ACCESS FULL                   | ITEM_MST                   |    39 |  2808 |     2   (0)| 00:00:01 |       |       |
|*  7 |   HASH JOIN                              |                            | 21056 |  7710K|   221   (1)| 00:00:01 |       |       |
|*  8 |    VIEW                                  |                            |   117 | 28899 |     2   (0)| 00:00:01 |       |       |
|   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D67CF_10BEAC9 |   117 | 11349 |     2   (0)| 00:00:01 |       |       |
|  10 |    PARTITION LIST SINGLE                 |                            |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|* 11 |     TABLE ACCESS FULL                    | SALES_TRN                  |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - Q2          
   4 - Q2           / S2@Q2
   6 - Q2           / S1@Q2
   8 - SEL$98BF2601 / S2@SEL$1
   9 - SEL$98BF2601 / T1@SEL$98BF2601
  11 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM" AND "S1"."CUST_ID"="S2"."CUST_ID")
   8 - filter(("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4'))
  11 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

マッピング表にないオブジェクト別名がOracleが作成したもの。CUST_MST（3行）とITEM_MST（39行）のMERGE JOIN CARTESIANした結果（3×39=117行）をPGAに溜めて、そのあとSALES_TRNとHASH_JOINしてるとおもう。TEMP TABLE TRANSFORMATIONは一時表（今回だとSYS_TEMP_0FD9D67CF_10BEAC9）をメモリ内で作成していることを示しているとおもう。

###サンプル⑥

####ヒントなし

sql（開く）

WITH sub1 AS (
	SELECT /*+ qb_name(q1) */
		s1.*
	FROM
		sales_trn s1
), sub2 AS (
	SELECT /*+ qb_name(q2) */
		s1.*
		, s2.*
	FROM
		item_mst s1
		, cust_mst s2
) SELECT
	s1.*
	, s2.*
  FROM
	sub1 s1
	, sub2 s2
	, sub2 s3
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.item = s2.item
	AND s1.cust_id = s2.cust_id
	AND s1.warehouse_code = s3.warehouse_code
	AND s1.item = s3.item
	AND s1.cust_id = s3.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |   233 (100)|          |       |       |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67D3_10BEAC9 |       |       |            |          |       |       |
|   3 |    MERGE JOIN CARTESIAN                  |                            |   117 | 11349 |     8   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS FULL                    | CUST_MST                   |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   5 |     BUFFER SORT                          |                            |    39 |  2808 |     5   (0)| 00:00:01 |       |       |
|   6 |      TABLE ACCESS FULL                   | ITEM_MST                   |    39 |  2808 |     2   (0)| 00:00:01 |       |       |
|*  7 |   HASH JOIN                              |                            |   821K|   326M|   225   (2)| 00:00:01 |       |       |
|*  8 |    VIEW                                  |                            |   117 |  4914 |     2   (0)| 00:00:01 |       |       |
|   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D67D3_10BEAC9 |   117 | 11349 |     2   (0)| 00:00:01 |       |       |
|* 10 |    HASH JOIN                             |                            | 21056 |  7710K|   221   (1)| 00:00:01 |       |       |
|* 11 |     VIEW                                 |                            |   117 | 28899 |     2   (0)| 00:00:01 |       |       |
|  12 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D67D3_10BEAC9 |   117 | 11349 |     2   (0)| 00:00:01 |       |       |
|  13 |     PARTITION LIST SINGLE                |                            |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|* 14 |      TABLE ACCESS FULL                   | SALES_TRN                  |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - Q2          
   4 - Q2           / S2@Q2
   6 - Q2           / S1@Q2
   8 - SEL$98BF2602 / S3@SEL$1
   9 - SEL$98BF2602 / T1@SEL$98BF2602
  11 - SEL$98BF2601 / S2@SEL$1
  12 - SEL$98BF2601 / T1@SEL$98BF2601
  14 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - access("S1"."WAREHOUSE_CODE"="S3"."WAREHOUSE_CODE" AND "S1"."ITEM"="S3"."ITEM" AND "S1"."CUST_ID"="S3"."CUST_ID")
   8 - filter(("S3"."WAREHOUSE_CODE"='w_C5MN' AND "S3"."ITEM"='p_0YL4'))
  10 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM" AND "S1"."CUST_ID"="S2"."CUST_ID")
  11 - filter(("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4'))
  14 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

一時表（今回だとSYS_TEMP_0FD9D67D3_10BEAC9）つかいまわしてんなー。

####ヒントあり

/*+ INLINE */

sql（開く）

WITH sub1 AS (
	SELECT /*+ qb_name(q1) */
		s1.*
	FROM
		sales_trn s1
), sub2 AS (
	SELECT /*+ qb_name(q2) inline */
		s1.*
		, s2.*
	FROM
		item_mst s1
		, cust_mst s2
) SELECT
	s1.*
	, s2.*
  FROM
	sub1 s1
	, sub2 s2
	, sub2 s3
  WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.item = s2.item
	AND s1.cust_id = s2.cust_id
	AND s1.warehouse_code = s3.warehouse_code
	AND s1.item = s3.item
	AND s1.cust_id = s3.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 |   118K|   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   291 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    72 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$9D506BF0
   3 - SEL$9D506BF0 / S1@Q2
   4 - SEL$9D506BF0 / S1@Q2
   5 - SEL$9D506BF0 / S2@Q2
   7 - SEL$9D506BF0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S1"."WAREHOUSE_CODE" AND "S1"."ITEM"="S1"."ITEM" AND 
              "S1"."CUST_ID"="S2"."CUST_ID")
   4 - access("S1"."WAREHOUSE_CODE"='w_C5MN' AND "S1"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

inlineヒントすげー！！オプティマイザが効率的なアクセス方法を選択できるように、選択肢の幅を広げているかんじかな。一時表なんて作る必要ないよ的な。テーブルに作成されているインデックスとかも見えるようにしている。

##NO_MERGE

ビューつくって確かめた。

sql（開く）

CREATE OR REPLACE VIEW sales_trn_item_mst AS
	SELECT
		s1.seq
		, s1.warehouse_code
		, s1.sales_seq
		, s1.voucher_clz
		, s1.correct_clz
		, s1.slip_no
		, s1.slip_row_no
		, s2.item
		, s1.prov_inst_qty
		, s1.cust_id
		, s1.arrive_date
		, s1.org_slip_no
		, s1.route_no
		, s1.route_order
		, s1.ship_date
		, s1.sales_date
		, s1.store_no
		, s2.item_kbn
		, s1.partition_key
	FROM
		sales_trn s1
		, item_mst s2
	WHERE
		s1.warehouse_code = s2.warehouse_code
		AND s1.item = s2.item;

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
	, s2.*
FROM
	sales_trn_item_mst s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$00C7B04A
   3 - SEL$00C7B04A / S2@SEL$1
   4 - SEL$00C7B04A / S2@SEL$1
   5 - SEL$00C7B04A / S2@Q1
   7 - SEL$00C7B04A / S1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@SEL$1	SALES_TRN
S2@SEL$1	ITEM_MST
S2@Q1	CUST_MST

コメント

Name列にSALES_TRN_ITEM_MSTViewが出力されていないから、View Merge生まれた。Viewに対してqb_nameヒントはViewをinlineにしないとむずかしいかな。Viewはinlineにしたほうがよさげ。。

####ヒントあり

/*+ NO_MERGE(<@queryblock> [tablespec]); */

sql（開く）

SELECT /*+ qb_name(q1) no_merge(s1) */
	s1.*
	, s2.*
FROM
	sales_trn_item_mst s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |       |       |   223 (100)|          |       |       |
|   1 |  MERGE JOIN                     |                    |   540 |   116K|   223   (1)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID   | CUST_MST           |     3 |    75 |     2   (0)| 00:00:01 |       |       |
|   3 |    INDEX FULL SCAN              | CUST_MST_PK        |     3 |       |     1   (0)| 00:00:01 |       |       |
|*  4 |   SORT JOIN                     |                    |   540 |   102K|   221   (1)| 00:00:01 |       |       |
|   5 |    VIEW                         | SALES_TRN_ITEM_MST |   540 |   102K|   220   (1)| 00:00:01 |       |       |
|   6 |     NESTED LOOPS                |                    |   540 | 77760 |   220   (1)| 00:00:01 |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID| ITEM_MST           |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  8 |       INDEX UNIQUE SCAN         | ITEM_MST_PK        |     1 |       |     0   (0)|          |       |       |
|   9 |      PARTITION LIST SINGLE      |                    |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|* 10 |       TABLE ACCESS FULL         | SALES_TRN          |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1   
   2 - Q1    / S2@Q1
   3 - Q1    / S2@Q1
   5 - SEL$1 / S1@Q1
   6 - SEL$1
   7 - SEL$1 / S2@SEL$1
   8 - SEL$1 / S2@SEL$1
  10 - SEL$1 / S1@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."CUST_ID"="S2"."CUST_ID")
       filter("S1"."CUST_ID"="S2"."CUST_ID")
   8 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
  10 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@SEL$1	SALES_TRN
S2@SEL$1	ITEM_MST
S2@Q1	CUST_MST
S1@Q1	SALES_TRN_ITEM_MST

コメント

SALES_TRN_ITEM_MSTViewが生成されてからMerge Joinしている。CUST_MST_PKに対してINDEX FULL SCANが発生しているのは、ソート処理を回避するため（結合列に索引が定義され、かつNOT NULL制約が存在する場合に適用）。ちなみに、ソートを実行しながら結合を進めるため、SALES_TRN_ITEM_MSTに対するソート処理は不可避。

##LEADING USE_NL MERGE NO_NLJ_BATCHING NO_BATCH_TABLE_ACCESS_BY_ROWID USE_NL_WITH_INDEX

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

オブジェクト別名

表名（別名可）@問合せブロック名

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ブルーム・フィルタリングおもしろそう！こんどやってみよう。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]) */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q1 S2@Q2) use_nl(S2@Q1 S2@Q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |       |       |  1299 (100)|          |       |       |
|   1 |  NESTED LOOPS                  |             |   540 | 91260 |  1299   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                 |             |   540 | 91260 |  1299   (1)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                |             |   540 | 77760 |   759   (1)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE      |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |      TABLE ACCESS FULL         | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|   6 |     TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  7 |      INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|*  8 |    INDEX UNIQUE SCAN           | CUST_MST_PK |     1 |       |     0   (0)|          |       |       |
|   9 |   TABLE ACCESS BY INDEX ROWID  | CUST_MST    |     1 |    25 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S1@Q1
   6 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S2@Q1
   8 - SEL$E72608F0 / S2@Q2
   9 - SEL$E72608F0 / S2@Q2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   7 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   8 - access("S1"."CUST_ID"="S2"."CUST_ID")

狙う結合順序は以下です。

#1. SALES_TRNを駆動表、ITEM_MSTを内部表にしたNL
#2. #1の結合結果を駆動表、CUST_MSTを内部表にしたNL

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

SALES_TRNにfilter述語(id=5)をもとにテーブルフルスキャンして540行取得する（駆動表）。取得した行数分、ITEM_MST_PK（内部表）に対してINDEX UNIQUE SCANを繰り返す。INDEX UNIQUE SCANなので、行数は540行のまま。INDEX UNIQUE SCANして取得したrowidをもとにITEM_MSTにテーブルアクセスする。SALES_TRNアクセスした際にITEM_MST_PKへアクセスする際に必要な固定値（定数）は取得できるので、SALES_TRNは定数の提供者ともいえる。直前のNLの結果を駆動表とし、SALES_TRNから与えられる定数をもとにCUST_MST_PK（内部表）へINDEX UNIQUE SCANを実施。INDEX UNIQUE SCANなので、行数は540行のまま。最後に、CUST_MSTの主キー以外のカラムを取得するために、直前のNLの結果を駆動表とし、INDEX UNIQUE SCANして取得したrowidをもとにCUST_MSTにテーブルアクセスする。

###サンプル②

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

パーティション、きわめたい。。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q2 S2@Q1) use_nl(S2@Q2 S2@Q1) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |       |       |  1299 (100)|          |       |       |
|   1 |  NESTED LOOPS                  |             |   540 | 91260 |  1299   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                 |             |   540 | 91260 |  1299   (1)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                |             |   540 | 82620 |   759   (1)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE      |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |      TABLE ACCESS FULL         | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|   6 |     TABLE ACCESS BY INDEX ROWID| CUST_MST    |     1 |    25 |     1   (0)| 00:00:01 |       |       |
|*  7 |      INDEX UNIQUE SCAN         | CUST_MST_PK |     1 |       |     0   (0)|          |       |       |
|*  8 |    INDEX UNIQUE SCAN           | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   9 |   TABLE ACCESS BY INDEX ROWID  | ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S1@Q1
   6 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S2@Q2
   8 - SEL$E72608F0 / S2@Q1
   9 - SEL$E72608F0 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   7 - access("S1"."CUST_ID"="S2"."CUST_ID")
   8 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

狙う結合順序は以下です。

#1. SALES_TRNを駆動表、CUST_MSTを内部表にしたNL
#2. #1の結合結果を駆動表、ITEM_MSTを内部表にしたNL

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

SALES_TRNにfilter述語(id=5)をもとにテーブルフルスキャンして540行取得する（駆動表）。取得した行数分、CUST_MST_PK（内部表）に対してINDEX UNIQUE SCANを繰り返す。INDEX UNIQUE SCANなので、行数は540行のまま。INDEX UNIQUE SCANして取得したrowidをもとにCUST_MSTにテーブルアクセスする。SALES_TRNアクセスした際にCUST_MST_PKへアクセスする際に必要な固定値（定数）は取得できるので、SALES_TRNは定数の提供者ともいえる。直前のNLの結果を駆動表とし、SALES_TRNから与えられる定数をもとにITEM_MST_PK（内部表）へINDEX UNIQUE SCANを実施。INDEX UNIQUE SCANなので、行数は540行のまま。最後に、ITEM_MSTの主キー以外のカラムを取得するために、直前のNLの結果を駆動表とし、INDEX UNIQUE SCANして取得したrowidをもとにITEM_MSTにテーブルアクセスする。

###サンプル③

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |       |       |   224 (100)|          |       |       |
|*  1 |  HASH JOIN                            |             | 14037 |  2316K|   224   (1)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS FULL                   | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|*  3 |   HASH JOIN                           |             | 14037 |  1973K|   221   (1)| 00:00:01 |       |       |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_MST    |    13 |   208 |     2   (0)| 00:00:01 |       |       |
|*  5 |     INDEX RANGE SCAN                  | ITEM_MST_PK |    13 |       |     1   (0)| 00:00:01 |       |       |
|   6 |    PARTITION LIST SINGLE              |             | 42112 |  5264K|   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |     TABLE ACCESS FULL                 | SALES_TRN   | 42112 |  5264K|   219   (1)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   2 - SEL$E72608F0 / S2@Q2
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID")
   3 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   5 - access("S2"."WAREHOUSE_CODE"='w_C5MN')
   7 - filter("S1"."WAREHOUSE_CODE"='w_C5MN')

オブジェクト別名

表名（別名可）@問合せブロック名

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

実行計画が右下に行くにつれてインデントが深くなるのはright deep joinの傾向だな。ファクト表をProbeするよりもマスタをProbeしていくほうがたしかに効率よさそうだな。ハッシュ結合って結合対象の表のうち小さい表から読み込んでハッシュ表をPGAに作るんだ。CUST_MST3行、ITEM_MST13行、SALES_TRN云万行だもんな。ITEM_MST_PKにはwarehouse_codeがあるので、INDEX RANGE SCANが狙えた。CUST_MST_PKにはwarehouse_codeがない。だから最初のハッシュ結合はITEM_MST、次のハッシュ結合ではCUST_MSTを最初に読み込んだ。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S2@Q1 S1@Q1 S2@Q2) use_nl(S1@Q1 S2@Q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |       |       | 16866 (100)|          |       |       |
|   1 |  NESTED LOOPS                          |             | 14040 |  2317K| 16866   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                         |             | 14040 |  2317K| 16866   (1)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                        |             | 14040 |  1974K|  2823   (1)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_MST    |    13 |   208 |     2   (0)| 00:00:01 |       |       |
|*  5 |      INDEX RANGE SCAN                  | ITEM_MST_PK |    13 |       |     1   (0)| 00:00:01 |       |       |
|   6 |     PARTITION LIST SINGLE              |             |  1080 |   135K|   217   (1)| 00:00:01 |     3 |     3 |
|*  7 |      TABLE ACCESS FULL                 | SALES_TRN   |  1080 |   135K|   217   (1)| 00:00:01 |     3 |     3 |
|*  8 |    INDEX UNIQUE SCAN                   | CUST_MST_PK |     1 |       |     0   (0)|          |       |       |
|   9 |   TABLE ACCESS BY INDEX ROWID          | CUST_MST    |     1 |    25 |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S1@Q1
   8 - SEL$E72608F0 / S2@Q2
   9 - SEL$E72608F0 / S2@Q2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("S2"."WAREHOUSE_CODE"='w_C5MN')
   7 - filter(("S1"."ITEM"="S2"."ITEM" AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   8 - access("S1"."CUST_ID"="S2"."CUST_ID")

狙う結合順序は以下です。

#1. ITEM_MSTを駆動表、SALES_TRNを内部表にしたNL
#2. #1の結合結果を駆動表、CUST_MSTを内部表にしたNL

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ITEM_MST_PKにfilter述語(id=5)をもとにINDEX RANGE SCANして13行取得する（駆動表）。取得した行数分、SALES_TRN（内部表）に対してTABLE ACCESS FULLを繰り返す。1回当たり1080行取得。13回繰り返して14040行取得。（この時点で詰んでる。）SALES_TRNアクセスした際にCUST_MST_PKへアクセスする際に必要な固定値（定数）は取得できるので、直前のNLの結果（14040行）を駆動表とし、CUST_MST_PKに対してINDEX UNIQUE SCANを実施。（14040回。。）取得件数はINDEX UNIQUE SCANのため、14040行のまま。CUST_MSTの主キー以外のカラムを取得するために、直前のNLの結果（14040行）を駆動表とし、CUST_MST_PKから取得したrowidをもとにCUST_MSTに14040回TABLE ACCESS BY INDEX ROWIDを実施する。CBOがパフォーマンスがよい実行計画が立てられるようにmergeヒントを与えている。ただ、デフォルトでマージできるものはマージしようとすると思うから、明記してもしなくてもいいのかもしれない。mergeヒントとno_mergeヒントは性能調査のデバッグ用に使えるセットかもしれん。unnestヒントとno_unnestヒントのような関係で。そもそもそれはno_がつくヒント全般にいえるか。。

###サンプル④

####ヒントなし

sql（開く）

SELECT
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |   220 (100)|          |       |       |
|   1 |  NESTED LOOPS                |             |   540 | 77760 |   220   (1)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   4 |   PARTITION LIST SINGLE      |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |    TABLE ACCESS FULL         | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   2 - SEL$74528C06 / S2@Q1
   3 - SEL$74528C06 / S2@Q1
   5 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST

コメント

SALES_TRNに対するコストはTABLE ACCESS FULLだから高いけど、駆動表のITEM_MSTのループ回数は1回だけ。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ merge(s1) leading(S1@Q1 S2@Q1) use_nl(S2@Q1) */
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |   759 (100)|          |       |       |
|   1 |  NESTED LOOPS                |             |   540 | 77760 |   759   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS               |             |   540 | 77760 |   759   (1)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE     |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |     TABLE ACCESS FULL        | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |    INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   6 |   TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   4 - SEL$74528C06 / S1@Q1
   5 - SEL$74528C06 / S2@Q1
   6 - SEL$74528C06 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   5 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

狙う結合順序は以下です。

#1. SALES_TRNを駆動表、ITEM_MSTを内部表にしたNL

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST

コメント

ITEM_MST_PKに対するコストはINDEX UNIQUE SCANだから低いけど、駆動表のSALES_TRNのループ回数は540回。。そのあとにITEM_MSTの取得列のカラム取得するために、TABLE ACCESS BY INDEX ROWIDを540回繰り返している。。TABLE ACCESS BY INDEX ROWIDのコスト自体は低いけど。駆動表の回数を以下に減らすかって大事。SELECT POWER(540,2) AS loop_cnt FROM dual;回ループしたことになるのか。。コストも跳ね上がっています。

###サンプル⑤

番外編で

####ヒントなし

使いたいヒントあったから、leadingヒント,use_nlヒントをヒントなしで使ってます。

sql（開く）

SELECT /*+ qb_name(q1) leading(s1 s2) use_nl(s2) index(@q1 s2 loc_mst_pk) */
	s1.*
	, s2.*
FROM
	item_mst s1
	, loc_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code;

実行計画（開く）

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |    81 (100)|          |
|   1 |  NESTED LOOPS                |            |   507 | 50700 |    81   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |            |   507 | 50700 |    81   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | ITEM_MST   |    39 |  2808 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | LOC_MST_PK |    13 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| LOC_MST    |    13 |   364 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   3 - Q1 / S1@Q1
   4 - Q1 / S2@Q1
   5 - Q1 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	ITEM_MST
S2@Q1	LOC_MST

コメント

Oracle Database 11gからNested Loops join Batchingができる。NL結合は、内部表のINDEX RANGE SCANやINDEX UNIQUE SCANのコストが少なかろうと、駆動表の行数が多いと内部表にアクセスするデータが多くなってしまう。そこで、NL結合を2回に分け、1回目は駆動表と内部表の索引スキャン、2回目は1回目の結果を駆動表にして内部表のデータアクセスというように、内部表の必要なデータをまとめてアクセスできるので、INDEX RANGE SCANやINDEX UNIQUE SCANでも効果的に行うことが可能になるらしい。。

####ヒントあり

/*+ NO_NLJ_BATCHING(<@queryblock> [tablespec]); */

sql（開く）

SELECT /*+ qb_name(q1) leading(s1 s2) use_nl(s2) index(@q1 s2 loc_mst_pk) no_nlj_batching(@q1 s2) */
	s1.*
	, s2.*
FROM
	item_mst s1
	, loc_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code;

実行計画（開く）

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |       |       |    81 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LOC_MST    |    13 |   364 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                      |            |   507 | 50700 |    81   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                | ITEM_MST   |    39 |  2808 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                 | LOC_MST_PK |    13 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1 / S2@Q1
   3 - Q1 / S1@Q1
   4 - Q1 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	ITEM_MST
S2@Q1	LOC_MST

コメント

Oracle9iからMulti Join Key Pre-fetchingが使えるそうです。NL結合の駆動表の1行に対して、内部表をINDEX RANGE SCANする場合に、Pre-fetchアクセスを行います。id=4で対象のROWIDをすべて取得してから、id=1でそのデータ・ブロックを先読みします。そのため、INDEX RANGE SCANでアクセスするデータ・ブロック数が多いと効果的。

###サンプル⑥

番外編で

####ヒントなし

使ってみたいヒントあったから、leadingヒント,use_nlヒント、no_nlj_batchingをヒントなしで使ってます。

/*+ NO_NLJ_BATCHING(<@queryblock> [tablespec]); */
/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */
/*+ INDEX([@queryblock] <tablespec> <index_name>) */

sql（開く）

SELECT /*+ qb_name(q1) leading(s1 s2) use_nl(s2) index(@q1 s2 loc_mst_pk) no_nlj_batching(@q1 s2) */
	s1.*
	, s2.*
FROM
	item_mst s1
	, loc_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code;

実行計画（開く）

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |       |       |    81 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LOC_MST    |    13 |   364 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                      |            |   507 | 50700 |    81   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                | ITEM_MST   |    39 |  2808 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                 | LOC_MST_PK |    13 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1 / S2@Q1
   3 - Q1 / S1@Q1
   4 - Q1 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	ITEM_MST
S2@Q1	LOC_MST

コメント

Oracle9iからMulti Join Key Pre-fetchingが使えるそうです。NL結合の駆動表の1行に対して、内部表をINDEX RANGE SCANする場合に、Pre-fetchアクセスを行います。id=4で対象のROWIDをすべて取得してから、id=1でそのデータ・ブロックを先読みします。そのため、INDEX RANGE SCANでアクセスするデータ・ブロック数が多いと効果的。

####ヒントあり

/*+ NO_BATCH_TABLE_ACCESS_BY_ROWID(<@queryblock> [tablespec]); */
/*+ NO_NLJ_BATCHING(<@queryblock> [tablespec]); */
/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */
/*+ INDEX([@queryblock] <tablespec> <index_name>) */

sql（開く）

SELECT /*+ qb_name(q1) leading(s1 s2) use_nl(s2) index(@q1 s2 loc_mst_pk) no_nlj_batching(@q1 s2) no_batch_table_access_by_rowid(@q1 s2) */
	s1.*
	, s2.*
FROM
	item_mst s1
	, loc_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code;

実行計画（開く）

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |    81 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| LOC_MST    |    13 |   364 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |            |   507 | 50700 |    81   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | ITEM_MST   |    39 |  2808 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | LOC_MST_PK |    13 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1 / S2@Q1
   3 - Q1 / S1@Q1
   4 - Q1 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	ITEM_MST
S2@Q1	LOC_MST

コメント

NO_BATCH_TABLE_ACCESS_BY_ROWIDヒントをつけることでLOC_MSTに対してTABLE ACCESS BY INDEX ROWID BATCHEDからTABLE ACCESS BY INDEX ROWIDへアクセス方法が変わった。また、Batchアクセスは、索引の順番を保障しないぽいらしい。Order by句つけても、インデックスの並び順をうまく使おうとしてBatchアクセスは行われない。

###サンプル⑦

番外編で

####ヒントなし

/*+ NO_NLJ_BATCHING(<@queryblock> [tablespec]); */
/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ USE_NL([@queryblock] <tablespec> <tablespec>) */
/*+ INDEX([@queryblock] <tablespec> <index_name>) */

sql（開く）

SELECT /*+ qb_name(q1) */
	*
FROM
	loc_mst s1
	, zone_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.zone = s2.zone;

実行計画（開く）

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |     6 (100)|          |
|   1 |  MERGE JOIN                  |             |    39 |  1560 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ZONE_MST    |    22 |   264 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | ZONE_MST_PK |    22 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |    39 |  1092 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | LOC_MST     |    39 |  1092 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   2 - Q1 / S2@Q1
   3 - Q1 / S2@Q1
   5 - Q1 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ZONE"="S2"."ZONE")
       filter(("S1"."ZONE"="S2"."ZONE" AND 
              "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE"))

オブジェクト別名

表名（別名可）@問合せブロック名

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	LOC_MST
S2@Q1	ZONE_MST

コメント

MERGE JOIN。。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ USE_NL_WITH_INDEX([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q1) leading(s1 s2) use_nl_with_index(@q1 s2 zone_mst_pk) */
	*
FROM
	loc_mst s1
	, zone_mst s2
WHERE
	s1.warehouse_code = s2.warehouse_code
	AND s1.zone = s2.zone;

実行計画（開く）

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |    42 (100)|          |
|   1 |  NESTED LOOPS                |             |    39 |  1560 |    42   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |    39 |  1560 |    42   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | LOC_MST     |    39 |  1092 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | ZONE_MST_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| ZONE_MST    |     1 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   3 - Q1 / S1@Q1
   4 - Q1 / S2@Q1
   5 - Q1 / S2@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ZONE"="S2"."ZONE")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	LOC_MST
S2@Q1	ZONE_MST

コメント

USE_NL_WITH_INDEXヒントいいかんじやな。

##LEADING USE_HASH

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.warehouse_code
	, s1.sales_date
	, s1.cust_id
	, s1.route_no
	, s1.store_no
	, SUM(
		CASE
			WHEN s1.item_kbn = '1' THEN 1
			ELSE 0
		END
	) AS item_kbn_1_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '2' THEN 1
			ELSE 0
		END
	) AS item_kbn_2_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '3' THEN 1
			ELSE 0
		END
	) AS item_kbn_3_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '4' THEN 1
			ELSE 0
		END
	) AS item_kbn_4_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '5' THEN 1
			ELSE 0
		END
	) AS item_kbn_5_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '6' THEN 1
			ELSE 0
		END
	) AS item_kbn_6_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '7' THEN 1
			ELSE 0
		END
	) AS item_kbn_7_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '8' THEN 1
			ELSE 0
		END
	) AS item_kbn_8_cnt
	, SUM(
		CASE
			WHEN s1.item_kbn = '9' THEN 1
			ELSE 0
		END
	) AS item_kbn_9_cnt
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s2.main_loc
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
	, store_mst s3
	, loc_mst s4
	, zone_mst s5
	, basket_mst s6
	, cart_mst s7
WHERE
	s1.cust_id = s2.cust_id
	AND s1.store_no = s3.store_no
	AND s1.warehouse_code = s4.warehouse_code
	AND s1.main_loc = s4.loc
	AND s1.warehouse_code = s5.warehouse_code
	AND s4.zone = s5.zone
	AND s2.basket_no = s6.basket_no
	AND s2.cart_no = s7.cart_no
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.partition_key = 'w_0003'
GROUP BY
	s1.warehouse_code
	, s1.sales_date
	, s1.cust_id
	, s1.route_no
	, s1.store_no;

実行計画（開く）

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |               |       |       |   228 (100)|          |       |       |
|   1 |  HASH GROUP BY                           |               |   100 | 14900 |   228   (2)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                           |               | 13271 |  1931K|   227   (1)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                          |               | 13271 |  1840K|   227   (1)| 00:00:01 |       |       |
|*  4 |     HASH JOIN                            |               | 14037 |  1809K|   226   (1)| 00:00:01 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED | LOC_MST       |    13 |   234 |     2   (0)| 00:00:01 |       |       |
|*  6 |       INDEX RANGE SCAN                   | LOC_MST_PK    |    13 |       |     1   (0)| 00:00:01 |       |       |
|*  7 |      HASH JOIN                           |               | 14037 |  1562K|   224   (1)| 00:00:01 |       |       |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| ITEM_MST      |    13 |   312 |     2   (0)| 00:00:01 |       |       |
|*  9 |        INDEX RANGE SCAN                  | ITEM_MST_PK   |    13 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |       HASH JOIN                          |               | 42112 |  3701K|   222   (1)| 00:00:01 |       |       |
|  11 |        NESTED LOOPS                      |               |     3 |   117 |     3   (0)| 00:00:01 |       |       |
|  12 |         NESTED LOOPS                     |               |     3 |    96 |     3   (0)| 00:00:01 |       |       |
|  13 |          TABLE ACCESS FULL               | CUST_MST      |     3 |    69 |     3   (0)| 00:00:01 |       |       |
|* 14 |          INDEX UNIQUE SCAN               | BASKET_MST_PK |     1 |     9 |     0   (0)|          |       |       |
|* 15 |         INDEX UNIQUE SCAN                | CART_MST_PK   |     1 |     7 |     0   (0)|          |       |       |
|  16 |        PARTITION LIST SINGLE             |               | 42112 |  2097K|   219   (1)| 00:00:01 |     3 |     3 |
|* 17 |         TABLE ACCESS FULL                | SALES_TRN     | 42112 |  2097K|   219   (1)| 00:00:01 |     3 |     3 |
|* 18 |     INDEX UNIQUE SCAN                    | ZONE_MST_PK   |     1 |    10 |     0   (0)|          |       |       |
|* 19 |    INDEX UNIQUE SCAN                     | STORE_MST_PK  |     1 |     7 |     0   (0)|          |       |       |
--------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S4@Q2
   6 - SEL$E72608F0 / S4@Q2
   8 - SEL$E72608F0 / S2@Q1
   9 - SEL$E72608F0 / S2@Q1
  13 - SEL$E72608F0 / S2@Q2
  14 - SEL$E72608F0 / S6@Q2
  15 - SEL$E72608F0 / S7@Q2
  17 - SEL$E72608F0 / S1@Q1
  18 - SEL$E72608F0 / S5@Q2
  19 - SEL$E72608F0 / S3@Q2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S1"."WAREHOUSE_CODE"="S4"."WAREHOUSE_CODE" AND "S2"."MAIN_LOC"="S4"."LOC")
   6 - access("S4"."WAREHOUSE_CODE"='w_C5MN')
   7 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   9 - access("S2"."WAREHOUSE_CODE"='w_C5MN')
  10 - access("S1"."CUST_ID"="S2"."CUST_ID")
  14 - access("S2"."BASKET_NO"="S6"."BASKET_NO")
  15 - access("S2"."CART_NO"="S7"."CART_NO")
  17 - filter("S1"."WAREHOUSE_CODE"='w_C5MN')
  18 - access("S5"."WAREHOUSE_CODE"='w_C5MN' AND "S4"."ZONE"="S5"."ZONE")
  19 - access("S1"."STORE_NO"="S3"."STORE_NO")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST
S4@Q2	LOC_MST
S5@Q2	ZONE_MST
S6@Q2	BASKET_MST
S7@Q2	STORE_MST

コメント

Right deep joinぽいな。HASH JOINのたびにインデント右下に深くなっているので。Probe表がなるべく小さな表(マスタ表)となるようにCBOが工夫して結合しているのであろう。

####ヒントあり

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q1 S2@Q2) use_hash(S2@Q1 S2@Q2) no_swap_join_inputs(S2@Q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                   |             |   540 | 77760 |   220   (1)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE      |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |     TABLE ACCESS FULL         | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|   5 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  6 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   7 |   TABLE ACCESS FULL           | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   4 - SEL$E72608F0 / S1@Q1
   5 - SEL$E72608F0 / S2@Q1
   6 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S2@Q2
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID")
   2 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   4 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   6 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

狙う結合順序は以下です。

#1. SALES_TRN（Build表）、ITEM_MST（Probe表）の順に結合した結果をハッシュテーブルとして作成
#2. #1で作成したハッシュテーブル（Build表）とCUST_MST（Probe表）を結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ねらいどおりきまった。

###サンプル②

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ITEM_MSTとCUST_MSTはNLでそのあとSALES_TRNとHASH JOINか。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q1 S2@Q2) use_hash(S2@Q1 S2@Q2) swap_join_inputs(S2@Q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS FULL           | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|*  3 |   HASH JOIN                   |             |   540 | 77760 |   220   (1)| 00:00:01 |       |       |
|   4 |    PARTITION LIST SINGLE      |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |     TABLE ACCESS FULL         | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|   6 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  7 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   2 - SEL$E72608F0 / S2@Q2
   5 - SEL$E72608F0 / S1@Q1
   6 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S2@Q1

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID")
   3 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   7 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

狙う結合順序は以下です。

#1. CUST_MST（Build表）をハッシュテーブルとして作成
#2. #1で作成したハッシュテーブル（Build表）とSALES_TRN（Build表）とCUST_MST（Probe表）の順に結合した結果（Probe表）を結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

狙い通り。

###サンプル③

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

Costは223。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q2 S2@Q1) use_hash(S2@Q2 S2@Q1) no_swap_join_inputs(S2@Q1) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                   |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                  |             |   540 | 82620 |   222   (1)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE     |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |     TABLE ACCESS FULL        | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|   5 |    TABLE ACCESS FULL         | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  7 |    INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   4 - SEL$E72608F0 / S1@Q1
   5 - SEL$E72608F0 / S2@Q2
   6 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S2@Q1

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   2 - access("S1"."CUST_ID"="S2"."CUST_ID")
   4 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   7 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

狙う結合順序は以下です。

#1. SALES_TRN（Build表）、CUST_MST（Probe表）の順に結合した結果をハッシュテーブルとして作成
#2. #1で作成したハッシュテーブル（Build表）とITEM_MST（Probe表）を結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ねらいどおり。Costは変わらず223。

###サンプル④

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |              |       |       |    23 (100)|          |       |       |
|   1 |  MERGE JOIN CARTESIAN                        |              |   225 | 32175 |    23   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST ALL                         |              |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | ITEM_MST     |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|*  4 |     INDEX RANGE SCAN                         | ITEM_MST_PK  |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|   5 |   BUFFER SORT                                |              |  1350 |   167K|    21   (0)| 00:00:01 |       |       |
|   6 |    PARTITION LIST SINGLE                     |              |  1350 |   167K|    21   (0)| 00:00:01 |     3 |     3 |
|*  7 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |  1350 |   167K|    21   (0)| 00:00:01 |     3 |     3 |
|*  8 |      INDEX SKIP SCAN                         | SALES_TRN_PK |  4050 |       |    21   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S1@Q1
   8 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter("S1"."ITEM"='p_0YL4')
   8 - access("S1"."WAREHOUSE_CODE"='w_C5MN' AND "S1"."PARTITION_KEY"='w_0003')

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST

コメント

MERGE JOIN CARTESIANが出現。。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ merge(s1) leading(S1@Q1 S2@Q1) use_hash(S2@Q1) swap_join_inputs(S2@Q1) */
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |       |       |    29 (100)|          |       |       |
|*  1 |  HASH JOIN                                  |             |   225 | 32175 |    29   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST ALL                        |             |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST    |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|*  4 |     INDEX RANGE SCAN                        | ITEM_MST_PK |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|   5 |   PARTITION LIST SINGLE                     |             |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|*  6 |    TABLE ACCESS FULL                        | SALES_TRN   |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$74528C06
   3 - SEL$74528C06 / S2@Q1
   4 - SEL$74528C06 / S2@Q1
   6 - SEL$74528C06 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   6 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

狙う結合順序は以下です。

#1. ITEM_MST（Build表）、SALES_TRN（Probe表）の順に結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ねらいどおりいったけど、コスト高くついた。。

###サンプル⑤

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |              |       |       |    23 (100)|          |       |       |
|   1 |  MERGE JOIN CARTESIAN                        |              |   225 | 32175 |    23   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST ALL                         |              |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | ITEM_MST     |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|*  4 |     INDEX RANGE SCAN                         | ITEM_MST_PK  |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|   5 |   BUFFER SORT                                |              |  1350 |   167K|    21   (0)| 00:00:01 |       |       |
|   6 |    PARTITION LIST SINGLE                     |              |  1350 |   167K|    21   (0)| 00:00:01 |     3 |     3 |
|*  7 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES_TRN    |  1350 |   167K|    21   (0)| 00:00:01 |     3 |     3 |
|*  8 |      INDEX SKIP SCAN                         | SALES_TRN_PK |  4050 |       |    21   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   7 - SEL$E72608F0 / S1@Q1
   8 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter("S1"."ITEM"='p_0YL4')
   8 - access("S1"."WAREHOUSE_CODE"='w_C5MN' AND "S1"."PARTITION_KEY"='w_0003')

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST

コメント

外部結合でもHash Joinの順番を制御できるのか。。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_HASH([@queryblock] <tablespec> <tablespec>) */
/*+ SWAP_JOIN_INPUTS(<@queryblock> [tablespec]); */
/*+ NO_SWAP_JOIN_INPUTS(<@queryblock> [tablespec]); */

sql（開く）

SELECT /*+ merge(s1) leading(S1@Q1 S2@Q1) use_hash(S2@Q1) swap_join_inputs(S2@Q1) */
	s1.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code (+)
			AND s1.item = s2.item (+)
	) s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |       |       |    29 (100)|          |       |       |
|*  1 |  HASH JOIN                                  |             |   225 | 32175 |    29   (0)| 00:00:01 |       |       |
|   2 |   PARTITION LIST ALL                        |             |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST    |     1 |    16 |     2   (0)| 00:00:01 |     1 |     3 |
|*  4 |     INDEX RANGE SCAN                        | ITEM_MST_PK |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|   5 |   PARTITION LIST SINGLE                     |             |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|*  6 |    TABLE ACCESS FULL                        | SALES_TRN   |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$BB0E799E
   3 - SEL$BB0E799E / S2@Q1
   4 - SEL$BB0E799E / S2@Q1
   6 - SEL$BB0E799E / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   6 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

狙う結合順序は以下です。

#1. ITEM_MST（Build表）、SALES_TRN（Probe表）の順に外部結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST

コメント

外部結合してもHash結合の順番制御できたな。

##LEADING USE_MERGE

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

TABLE ACCESS FULLはCost高いなー。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q1 S2@Q2) use_merge(S2@Q1 S2@Q2) */ s1.* ,s2.* FROM (SELECT /*+ qb_name(q1) */ s1.seq , s1.warehouse_code , s1.sales_seq , s1.voucher_clz , s1.correct_clz , s1.slip_no , s1.slip_row_no , s2.item , s1.prov_inst_qty , s1.cust_id , s1.arrive_date , s1.org_slip_no , s1.route_no , s1.route_order , s1.ship_date , s1.sales_date , s1.store_no , s2.item_kbn , s1.partition_key FROM sales_trn s1 ,item_mst s2 WHERE s1.warehouse_code = s2.warehouse_code and s1.item = s2.item) s1 ,cust_mst s2 WHERE s1.cust_id = s2.cust_id and s1.warehouse_code = 'w_C5MN' AND s1.item = 'p_0YL4'AND s1.partition_key = 'w_0003';

実行計画（開く）


---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |       |       |   226 (100)|          |       |       |
|   1 |  MERGE JOIN                     |             |   540 | 91260 |   226   (2)| 00:00:01 |       |       |
|   2 |   SORT JOIN                     |             |   540 | 77760 |   222   (2)| 00:00:01 |       |       |
|   3 |    MERGE JOIN                   |             |   540 | 77760 |   221   (1)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |      TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  6 |     SORT JOIN                   |             |     1 |    16 |     2  (50)| 00:00:01 |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  8 |       INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|*  9 |   SORT JOIN                     |             |     3 |    75 |     4  (25)| 00:00:01 |       |       |
|  10 |    TABLE ACCESS FULL            | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S1@Q1
   7 - SEL$E72608F0 / S2@Q1
   8 - SEL$E72608F0 / S2@Q1
  10 - SEL$E72608F0 / S2@Q2

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   6 - access("S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM")
       filter(("S1"."ITEM"="S2"."ITEM" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE"))
   8 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   9 - access("S1"."CUST_ID"="S2"."CUST_ID")
       filter("S1"."CUST_ID"="S2"."CUST_ID")

狙う結合順序は以下です。

#1. SALES_TRNとITEM_MSTの順に結合した結果をPGAに作成
#2. #1で作成した結果とCUST_MSTの順に結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ねらいどおり。

###サンプル②

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

id=4のINDEX UNIQUE SCANってコスト0なんだ。0になることもあるのか。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S1@Q1 S2@Q2 S2@Q1) use_merge(S2@Q2 S2@Q1) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   226 (100)|          |       |       |
|   1 |  MERGE JOIN                   |             |   540 | 91260 |   226   (2)| 00:00:01 |       |       |
|   2 |   MERGE JOIN                  |             |   540 | 82620 |   224   (2)| 00:00:01 |       |       |
|   3 |    SORT JOIN                  |             |   540 | 69120 |   220   (1)| 00:00:01 |       |       |
|   4 |     PARTITION LIST SINGLE     |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |      TABLE ACCESS FULL        | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  6 |    SORT JOIN                  |             |     3 |    75 |     4  (25)| 00:00:01 |       |       |
|   7 |     TABLE ACCESS FULL         | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|*  8 |   SORT JOIN                   |             |     1 |    16 |     2  (50)| 00:00:01 |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|* 10 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   5 - SEL$E72608F0 / S1@Q1
   7 - SEL$E72608F0 / S2@Q2
   9 - SEL$E72608F0 / S2@Q1
  10 - SEL$E72608F0 / S2@Q1

狙う結合順序は以下です。

#1. SALES_TRNとCUST_MSTの順に結合した結果をPGAに作成
#2. #1で作成した結果とITEM_MSTの順に結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

ねらいどおり。

###サンプル③

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q2) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |   223 (100)|          |       |       |
|*  1 |  HASH JOIN                    |             |   540 | 91260 |   223   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   5 |    TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|   6 |   PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  7 |    TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   3 - SEL$E72608F0 / S2@Q1
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q2
   7 - SEL$E72608F0 / S1@Q1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
   4 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

Cost列はOperationごとの総和で求めることができる。

####ヒントあり

/*+ LEADING([@queryblock] <table_name> <table_name>) */
/*+ MERGE(<@queryblock> [tablespec]); */
/*+ USE_MERGE([@queryblock] <tablespec> <tablespec>) */

sql（開く）

SELECT /*+ qb_name(q2) merge(s1) leading(S2@Q1 S2@Q2 S1@Q1) use_merge(S2@Q2 S1@Q1) */
	s1.*
	, s2.*
FROM
	(
		SELECT /*+ qb_name(q1) */
			s1.seq
			, s1.warehouse_code
			, s1.sales_seq
			, s1.voucher_clz
			, s1.correct_clz
			, s1.slip_no
			, s1.slip_row_no
			, s2.item
			, s1.prov_inst_qty
			, s1.cust_id
			, s1.arrive_date
			, s1.org_slip_no
			, s1.route_no
			, s1.route_order
			, s1.ship_date
			, s1.sales_date
			, s1.store_no
			, s2.item_kbn
			, s1.partition_key
		FROM
			sales_trn s1
			, item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	) s1
	, cust_mst s2
WHERE
	s1.cust_id = s2.cust_id
	AND s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |       |       |   225 (100)|          |       |       |
|   1 |  MERGE JOIN                    |             |   540 | 91260 |   225   (2)| 00:00:01 |       |       |
|   2 |   SORT JOIN                    |             |     3 |   123 |     5  (20)| 00:00:01 |       |       |
|   3 |    MERGE JOIN CARTESIAN        |             |     3 |   123 |     4   (0)| 00:00:01 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| ITEM_MST    |     1 |    16 |     1   (0)| 00:00:01 |       |       |
|*  5 |      INDEX UNIQUE SCAN         | ITEM_MST_PK |     1 |       |     0   (0)|          |       |       |
|   6 |     TABLE ACCESS FULL          | CUST_MST    |     3 |    75 |     3   (0)| 00:00:01 |       |       |
|*  7 |   SORT JOIN                    |             |   540 | 69120 |   220   (1)| 00:00:01 |       |       |
|   8 |    PARTITION LIST SINGLE       |             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  9 |     TABLE ACCESS FULL          | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
--------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$E72608F0
   4 - SEL$E72608F0 / S2@Q1
   5 - SEL$E72608F0 / S2@Q1
   6 - SEL$E72608F0 / S2@Q2
   9 - SEL$E72608F0 / S1@Q1

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   7 - access("S1"."CUST_ID"="S2"."CUST_ID" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."ITEM"="S2"."ITEM")
       filter(("S1"."ITEM"="S2"."ITEM" AND "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND 
              "S1"."CUST_ID"="S2"."CUST_ID"))
   9 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

狙う結合順序は以下です。

#1. ITEM_MSTとCUST_MSTの順に結合した結果をPGAに作成
#2. #1で作成した結果とSALES_TRNの順に結合

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q1	ITEM_MST
S2@Q2	CUST_MST

コメント

Costが1小さくなった！小さい表同士のMerge Joinはコストが小さめに収まるのかな。

##UNNEST NO_UNNEST

###サンプル①

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
	, CASE
			WHEN EXISTS (
				SELECT /*+ qb_name(q2) */
					1
				FROM
					item_mst s2
				WHERE
					s1.warehouse_code = s2.warehouse_code
					AND s1.item = s2.item
			) THEN 1
			ELSE 0
		END
	AS item_chk
	, CASE
			WHEN EXISTS (
				SELECT /*+ qb_name(q3) */
					1
				FROM
					cust_mst s2
				WHERE
					s1.cust_id = s2.cust_id
			) THEN 1
			ELSE 0
		END
	AS cust_chk
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |       |       |   219 (100)|          |       |       |
|*  1 |  INDEX UNIQUE SCAN    | ITEM_MST_PK |     1 |    14 |     0   (0)|          |       |       |
|*  2 |  INDEX UNIQUE SCAN    | CUST_MST_PK |     1 |     7 |     0   (0)|          |       |       |
|   3 |  PARTITION LIST SINGLE|             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |   TABLE ACCESS FULL   | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q2 / S2@Q2
   2 - Q3 / S2@Q3
   3 - Q1
   4 - Q1 / S1@Q1

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

NLがない！！select句に書くと。おぉー。

####ヒントあり

/*+ NO_INDEX([@queryblock] <tablespec> <indexspec>) */

sql（開く）

SELECT /*+ qb_name(q1) no_index(@q2 s2 item_mst_pk) no_index(@q3 s2 cust_mst_pk)*/
	s1.*
	, CASE
			WHEN EXISTS (
				SELECT /*+ qb_name(q2) */
					1
				FROM
					item_mst s2
				WHERE
					s1.warehouse_code = s2.warehouse_code
					AND s1.item = s2.item
			) THEN 1
			ELSE 0
		END
	AS item_chk
	, CASE
			WHEN EXISTS (
				SELECT /*+ qb_name(q3) */
					1
				FROM
					cust_mst s2
				WHERE
					s1.cust_id = s2.cust_id
			) THEN 1
			ELSE 0
		END
	AS cust_chk
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003';

実行計画（開く）

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |       |       |   231 (100)|          |       |       |
|*  1 |  TABLE ACCESS FULL    | ITEM_MST  |     1 |    14 |     3   (0)| 00:00:01 |       |       |
|*  2 |  TABLE ACCESS FULL    | CUST_MST  |     1 |     7 |     3   (0)| 00:00:01 |       |       |
|   3 |  PARTITION LIST SINGLE|           |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |   TABLE ACCESS FULL   | SALES_TRN |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q2 / S2@Q2
   2 - Q3 / S2@Q3
   3 - Q1
   4 - Q1 / S1@Q1

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("S2"."ITEM"=:B1 AND "S2"."WAREHOUSE_CODE"=:B2))
   2 - filter("S2"."CUST_ID"=:B1)
   4 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))

マッピング表

オブジェクト別名	テーブル名
S1@Q1	SALES_TRN
S1@Q2	ITEM_MST
S1@Q3	CUST_MST

コメント

where句以外のEXISTSはUNNEST、NO_UNNESTの適用なし。id=1,2のoperationがINDEX UNIQUE SCANからTABLE ACCESS FULLになった！！

###サンプル②

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND EXISTS (
		SELECT /*+ qb_name(q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	)
	AND EXISTS (
		SELECT /*+ qb_name(q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
	);

実行計画（開く）

-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |   219 (100)|          |       |       |
|   1 |  NESTED LOOPS           |             |    14 |  2086 |   219   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS          |             |    14 |  1988 |   219   (1)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN    | ITEM_MST_PK |     1 |    14 |     0   (0)|          |       |       |
|   4 |    PARTITION LIST SINGLE|             |    14 |  1792 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |     TABLE ACCESS FULL   | SALES_TRN   |    14 |  1792 |   219   (1)| 00:00:01 |     3 |     3 |
|*  6 |   INDEX UNIQUE SCAN     | CUST_MST_PK |     1 |     7 |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$95D2DB43
   3 - SEL$95D2DB43 / S2@Q2
   5 - SEL$95D2DB43 / S1@Q1
   6 - SEL$95D2DB43 / S2@Q3

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
   5 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN' AND 
              "S1"."WAREHOUSE_CODE"="S2"."WAREHOUSE_CODE" AND "S1"."ITEM"="S2"."ITEM"))
   6 - access("S1"."CUST_ID"="S2"."CUST_ID")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST

コメント

NLうまれた。

####ヒントあり

/*+ NO_UNNEST(<@queryblock>) */
/*+ UNNEST(<@queryblock>) */

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND EXISTS (
		SELECT /*+ qb_name(q2) no_unnest(@q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	)
	AND EXISTS (
		SELECT /*+ qb_name(q3) no_unnest(@q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
	);

実行計画（開く）

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |   219 (100)|          |       |       |
|*  1 |  FILTER                |             |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL   | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |   INDEX UNIQUE SCAN    | ITEM_MST_PK |     1 |    14 |     0   (0)|          |       |       |
|*  5 |   INDEX UNIQUE SCAN    | CUST_MST_PK |     1 |     7 |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   3 - Q1 / S1@Q1
   4 - Q2 / S2@Q2
   5 - Q3 / S2@Q3
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(( IS NOT NULL AND  IS NOT NULL))
   3 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   4 - access("S2"."WAREHOUSE_CODE"=:B1 AND "S2"."ITEM"=:B2)
   5 - access("S2"."CUST_ID"=:B1)

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST

コメント

FILTERうまれた。id=1のfilterみると、IS NOT NULL AND IS NOT NULLとあるから完全外部結合してともにNULLでない行を取得しようしているとおもわれる。

###サンプル③

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND NOT EXISTS (
		SELECT /*+ qb_name(q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	)
	AND NOT EXISTS (
		SELECT /*+ qb_name(q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
	);

実行計画（開く）

-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       |       |   219 (100)|          |       |       |
|   1 |  NESTED LOOPS ANTI      |             |     5 |   745 |   219   (1)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS ANTI     |             |     5 |   675 |   219   (1)| 00:00:01 |       |       |
|   3 |    PARTITION LIST SINGLE|             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |     TABLE ACCESS FULL   | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  5 |    INDEX UNIQUE SCAN    | CUST_MST_PK |     3 |    21 |     0   (0)|          |       |       |
|*  6 |   INDEX UNIQUE SCAN     | ITEM_MST_PK |     1 |    14 |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$95D2DB43
   4 - SEL$95D2DB43 / S1@Q1
   5 - SEL$95D2DB43 / S2@Q3
   6 - SEL$95D2DB43 / S2@Q2

Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   5 - access("S1"."CUST_ID"="S2"."CUST_ID")
   6 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST

コメント

NL ANTIうまれた。

####ヒントあり

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND NOT EXISTS (
		SELECT /*+ qb_name(q2) no_unnest(@q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
	)
	AND NOT EXISTS (
		SELECT /*+ qb_name(q3) no_unnest(@q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
	);

実行計画（開く）

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |   219 (100)|          |       |       |
|*  1 |  FILTER                |             |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|             |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL   | SALES_TRN   |   540 | 69120 |   219   (1)| 00:00:01 |     3 |     3 |
|*  4 |   INDEX UNIQUE SCAN    | ITEM_MST_PK |     1 |    14 |     0   (0)|          |       |       |
|*  5 |   INDEX UNIQUE SCAN    | CUST_MST_PK |     1 |     7 |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   3 - Q1 / S1@Q1
   4 - Q2 / S2@Q2
   5 - Q3 / S2@Q3
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(( IS NULL AND  IS NULL))
   3 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   4 - access("S2"."WAREHOUSE_CODE"=:B1 AND "S2"."ITEM"=:B2)
   5 - access("S2"."CUST_ID"=:B1)

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST

コメント

FILTERうまれた。id=1のfilterみると、IS NULL AND IS NULLとあるから完全外部結合してともにNULLの行を取得しようしているとおもわれる。ここでわかることはno_unnestすると、完全外部結合して、そのあと論理演算子にしたがい、filteringするということだろう。きっと。

###サンプル④

####ヒントなし

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND NOT EXISTS (
		SELECT /*+ qb_name(q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
			AND EXISTS (
				SELECT /*+ qb_name(q4) */
					1
				FROM
					loc_mst s3
				WHERE
					s2.warehouse_code = s3.warehouse_code
					AND s2.main_loc = s3.loc
					AND EXISTS (
						SELECT /*+ qb_name(q5) */
							1
						FROM
							zone_mst s4
						WHERE
							s3.warehouse_code = s4.warehouse_code
							AND s3.zone = s4.zone
					)
			)
	)
	AND NOT EXISTS (
		SELECT /*+ qb_name(q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
			AND EXISTS (
				SELECT /*+ qb_name(q6) */
					1
				FROM
					cart_mst s3
				WHERE
					s2.cart_no = s3.cart_no
			)
			AND EXISTS (
				SELECT /*+ qb_name(q7) */
					1
				FROM
					basket_mst s3
				WHERE
					s2.basket_no = s3.basket_no
			)
	);

実行計画（開く）

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |               |       |       |    33 (100)|          |       |       |
|*  1 |  FILTER                                         |               |       |       |            |          |       |       |
|*  2 |   HASH JOIN RIGHT ANTI                          |               |  1350 |   204K|    30   (0)| 00:00:01 |       |       |
|   3 |    VIEW                                         | VW_SQ_1       |     1 |    28 |     3   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS SEMI                           |               |     1 |    50 |     3   (0)| 00:00:01 |       |       |
|   5 |      NESTED LOOPS                               |               |     1 |    40 |     3   (0)| 00:00:01 |       |       |
|   6 |       PARTITION LIST ALL                        |               |     1 |    22 |     2   (0)| 00:00:01 |     1 |     3 |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST      |     1 |    22 |     2   (0)| 00:00:01 |     1 |     3 |
|*  8 |         INDEX RANGE SCAN                        | ITEM_MST_PK   |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|   9 |       TABLE ACCESS BY INDEX ROWID               | LOC_MST       |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|* 10 |        INDEX UNIQUE SCAN                        | LOC_MST_PK    |     1 |       |     0   (0)|          |       |       |
|* 11 |      INDEX UNIQUE SCAN                          | ZONE_MST_PK   |     1 |    10 |     0   (0)|          |       |       |
|  12 |    PARTITION LIST SINGLE                        |               |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|* 13 |     TABLE ACCESS FULL                           | SALES_TRN     |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|  14 |   NESTED LOOPS                                  |               |     1 |    39 |     1   (0)| 00:00:01 |       |       |
|  15 |    NESTED LOOPS                                 |               |     1 |    30 |     1   (0)| 00:00:01 |       |       |
|  16 |     TABLE ACCESS BY INDEX ROWID                 | CUST_MST      |     1 |    23 |     1   (0)| 00:00:01 |       |       |
|* 17 |      INDEX UNIQUE SCAN                          | CUST_MST_PK   |     1 |       |     0   (0)|          |       |       |
|* 18 |     INDEX UNIQUE SCAN                           | CART_MST_PK   |     1 |     7 |     0   (0)|          |       |       |
|* 19 |    INDEX UNIQUE SCAN                            | BASKET_MST_PK |     1 |     9 |     0   (0)|          |       |       |
---------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$50A7D8DB
   3 - SEL$790B7CFB / VW_SQ_1@SEL$D2BBBC82
   4 - SEL$790B7CFB
   7 - SEL$790B7CFB / S2@Q2
   8 - SEL$790B7CFB / S2@Q2
   9 - SEL$790B7CFB / S3@Q4
  10 - SEL$790B7CFB / S3@Q4
  11 - SEL$790B7CFB / S4@Q5
  13 - SEL$50A7D8DB / S1@Q1
  14 - SEL$E0B70A47
  16 - SEL$E0B70A47 / S2@Q3
  17 - SEL$E0B70A47 / S2@Q3
  18 - SEL$E0B70A47 / S3@Q6
  19 - SEL$E0B70A47 / S3@Q7
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NULL)
   2 - access("S1"."WAREHOUSE_CODE"="ITEM_1" AND "S1"."ITEM"="ITEM_2")
   8 - access("S2"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."ITEM"='p_0YL4')
  10 - access("S3"."WAREHOUSE_CODE"='w_C5MN' AND "S2"."MAIN_LOC"="S3"."LOC")
  11 - access("S4"."WAREHOUSE_CODE"='w_C5MN' AND "S3"."ZONE"="S4"."ZONE")
       filter("S3"."WAREHOUSE_CODE"="S4"."WAREHOUSE_CODE")
  13 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
  17 - access("S2"."CUST_ID"=:B1)
  18 - access("S2"."CART_NO"="S3"."CART_NO")
  19 - access("S2"."BASKET_NO"="S3"."BASKET_NO")

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST
S3@Q4	LOC_MST
S3@Q4	LOC_MST
S4@Q5	ZONE_MST
S3@Q6	CART_MST
S3@Q7	BASKET_MST

コメント

NESTED LOOPS SEMI、HASH JOIN RIGHT ANTIでた！！

####ヒントあり

sql（開く）

SELECT /*+ qb_name(q1) */
	s1.*
FROM
	sales_trn s1
WHERE
	s1.warehouse_code = 'w_C5MN'
	AND s1.item = 'p_0YL4'
	AND s1.partition_key = 'w_0003'
	AND NOT EXISTS (
		SELECT /*+ qb_name(q2) no_unnest(@q2) */
			1
		FROM
			item_mst s2
		WHERE
			s1.warehouse_code = s2.warehouse_code
			AND s1.item = s2.item
			AND EXISTS (
				SELECT /*+ qb_name(q4) no_unnest(@q4) */
					1
				FROM
					loc_mst s3
				WHERE
					s2.warehouse_code = s3.warehouse_code
					AND s2.main_loc = s3.loc
					AND EXISTS (
						SELECT /*+ qb_name(q5) no_unnest(@q5) */
							1
						FROM
							zone_mst s4
						WHERE
							s3.warehouse_code = s4.warehouse_code
							AND s3.zone = s4.zone
					)
			)
	)
	AND NOT EXISTS (
		SELECT /*+ qb_name(q3) no_unnest(@q3) */
			1
		FROM
			cust_mst s2
		WHERE
			s1.cust_id = s2.cust_id
			AND EXISTS (
				SELECT /*+ qb_name(q6) no_unnest(@q6) */
					1
				FROM
					cart_mst s3
				WHERE
					s2.cart_no = s3.cart_no
			)
			AND EXISTS (
				SELECT /*+ qb_name(q7) no_unnest(@q7) */
					1
				FROM
					basket_mst s3
				WHERE
					s2.basket_no = s3.basket_no
			)
	);

実行計画（開く）

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |       |       |    31 (100)|          |       |       |
|*  1 |  FILTER                                      |               |       |       |            |          |       |       |
|   2 |   PARTITION LIST SINGLE                      |               |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL                         | SALES_TRN     |  1350 |   167K|    27   (0)| 00:00:01 |     3 |     3 |
|*  4 |   FILTER                                     |               |       |       |            |          |       |       |
|   5 |    PARTITION LIST ALL                        |               |     1 |    22 |     2   (0)| 00:00:01 |     1 |     3 |
|   6 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ITEM_MST      |     1 |    22 |     2   (0)| 00:00:01 |     1 |     3 |
|*  7 |      INDEX RANGE SCAN                        | ITEM_MST_PK   |     1 |       |     1   (0)| 00:00:01 |     1 |     3 |
|*  8 |    FILTER                                    |               |       |       |            |          |       |       |
|   9 |     TABLE ACCESS BY INDEX ROWID              | LOC_MST       |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|* 10 |      INDEX UNIQUE SCAN                       | LOC_MST_PK    |     1 |       |     0   (0)|          |       |       |
|* 11 |     INDEX UNIQUE SCAN                        | ZONE_MST_PK   |     1 |    10 |     0   (0)|          |       |       |
|* 12 |   FILTER                                     |               |       |       |            |          |       |       |
|  13 |    TABLE ACCESS BY INDEX ROWID               | CUST_MST      |     1 |    23 |     1   (0)| 00:00:01 |       |       |
|* 14 |     INDEX UNIQUE SCAN                        | CUST_MST_PK   |     1 |       |     0   (0)|          |       |       |
|* 15 |    INDEX UNIQUE SCAN                         | CART_MST_PK   |     1 |     7 |     0   (0)|          |       |       |
|* 16 |    INDEX UNIQUE SCAN                         | BASKET_MST_PK |     1 |     9 |     0   (0)|          |       |       |
------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - Q1
   3 - Q1 / S1@Q1
   4 - Q2
   6 - Q2 / S2@Q2
   7 - Q2 / S2@Q2
   8 - Q4
   9 - Q4 / S3@Q4
  10 - Q4 / S3@Q4
  11 - Q5 / S4@Q5
  12 - Q3
  13 - Q3 / S2@Q3
  14 - Q3 / S2@Q3
  15 - Q6 / S3@Q6
  16 - Q7 / S3@Q7
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(( IS NULL AND  IS NULL))
   3 - filter(("S1"."ITEM"='p_0YL4' AND "S1"."WAREHOUSE_CODE"='w_C5MN'))
   4 - filter( IS NOT NULL)
   7 - access("S2"."WAREHOUSE_CODE"=:B1 AND "S2"."ITEM"=:B2)
   8 - filter( IS NOT NULL)
  10 - access("S3"."WAREHOUSE_CODE"=:B1 AND "S3"."LOC"=:B2)
  11 - access("S4"."WAREHOUSE_CODE"=:B1 AND "S4"."ZONE"=:B2)
  12 - filter(( IS NOT NULL AND  IS NOT NULL))
  14 - access("S2"."CUST_ID"=:B1)
  15 - access("S3"."CART_NO"=:B1)
  16 - access("S3"."BASKET_NO"=:B1)

マッピング表

オブジェクト別名	オブジェクト名
S1@Q1	SALES_TRN
S2@Q2	ITEM_MST
S2@Q3	CUST_MST
S3@Q4	LOC_MST
S3@Q4	LOC_MST
S4@Q5	ZONE_MST
S3@Q6	CART_MST
S3@Q7	BASKET_MST

コメント

no_unnestするとCostに差がでやすいかも。。比較しやすい。debugにつかえるかも。

#あとがき
後半はもう力尽きてしまいましたが、始める前よりはいろんな知識が広く浅く身についた気だけします。。
コメントが浅いところはまた今度掘り下げよう。sql書くときに実行計画もイメージできたら楽しそうだなーと今回の遊びを通じて感じました。Dockerとか最近やってて開発環境とか楽に準備できるようになったので、Oracleでおもいっきり遊べる環境ができて、いい感じです。（Dockerは最近知った。）DBLINKとかした際の実行計画とかも見てみたいなーと思う。

以上、ありがとうございました。

#参考文献

[@BLEVEL LEAF_BLOCKS](http://oracle.se-free.com/dic/E4_indexes.html
[@Batching NLJに対するOracleマニュアルの説明](http://dioncho.blogspot.com/2010/10/batching-nljoracle.html
[@Batching NLJ最適化と整列](http://dioncho.blogspot.com/2010/08/batching-nlj.html
[@EXISTSまたはIN](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-29-1982534-ja.html
[@GATHER_TABLE_STATS](https://www.morganslibrary.org/reference/pkgs/dbms_stats.html
[@GATHER_TABLE_STATSプロシージャ](https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_stats.htm#i1036461
[@Hash Joins(ハッシュ結合)](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@INDEX FULL SCAN](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@INDEX RANGE SCAN](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@INDEX SKIP SCAN](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@INDEX UNIQUE SCAN](http://www.undercoverlog.com/entry/2018/06/02/Oracle_B-tree%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9
[@INDEX UNIQUE SCAN](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@INとEXISTSについて](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm09-1598259-ja.html
[@LEADING](https://www.drk7.jp/MT/archives/001425.html
[@Left-deep JoinとRight-deep Joinについて](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-46-2547814-ja.html
@Left-deep JoinとRight-deep Joinについて
[@Merge Join Cartesian(デカルト積)](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@NESTED LOOPS](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@NOT EXISTS](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-29-1982534-ja.html
[@Nested Loops Joins(ネステッド・ループ結合)](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@Oracle B-treeインデックスアクセス)](http://www.undercoverlog.com/entry/2018/06/02/Oracle_B-tree%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9
[@Oracle Partitioned Tables and Indexes](https://www.morganslibrary.org/reference/partitions.html
@Oracle Partitioned Tables and Indexes
[@Oracle 問合せ変換②-1 ビュー・マージ(Simple View Merging)](http://www.undercoverlog.com/entry/2018/06/23/Oracle_%E5%95%8F%E5%90%88%E3%81%9B%E5%A4%89%E6%8F%9B%E2%91%A1-1_%E3%83%93%E3%83%A5%E3%83%BC%E3%83%BB%E3%83%9E%E3%83%BC%E3%82%B8%28Simple_View_Merging%29
[@Pstart Pstop](https://www.oracle.com/technetwork/jp/database/articles/tsushima/ndex-1868009-ja.html
[@QB_NAME – control execution plans in views](http://dbaora.com/qb_name-control-execution-plans-in-views/
[@Query Transformation](http://d.hatena.ne.jp/yohei-a/20110224/1298529324
[@SWAP_JOIN_INPUTS](http://www.doppo1.net/oracle/tuning/hash-join-hint.html
[@Sort Merge Joins(ソート・マージ結合)](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@TABLE ACCESS BY INDEX ROWID BATCHED](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@TABLE ACCESS BY INDEX ROWID BATCHED](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-34-2135613-ja.html
[@TABLE ACCESS BY INDEX ROWID](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@TABLE ACCESS BY LOCAL INDEX ROWID](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@TABLE ACCESS FULL](http://www.undercoverlog.com/entry/2018/07/19/Oracle_%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%91%E3%82%B9
[@TEMP TABLE TRANSFORMATION](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-60-3763796-ja.html
[@UNNEST NO_UNNEST](https://www.shift-the-oracle.com/inside/in-exists-difference.html
[@View Merging](http://d.hatena.ne.jp/yohei-a/20110224/1298529324
[@Viewのマージ](https://qiita.com/tontan9616/items/e116a8a945bb26c2cc46
[@WITH clause and hints MATERIALIZE and INLINE](http://dbaora.com/with-clause-and-hints-materialize-and-inline/
[@アンチジョイン](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@アンチジョイン](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm09-1598259-ja.html
[@オプティマイザ統計の収集](https://www.oracle.com/technetwork/jp/database/articles/tsushima/index-33-2100813-ja.html
[@セミジョイン](http://www.undercoverlog.com/entry/2018/06/05/Oracle_%E7%B5%90%E5%90%88%E6%96%B9%E6%B3%95%E3%81%A8%E7%B5%90%E5%90%88%E3%82%BF%E3%82%A4%E3%83%97
[@ネストされた副問合せのネスト解除](https://docs.oracle.com/cd/E16338_01/server.112/b56299/queries008.htm
[@ネストした副問合せの解除](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-29-1982534-ja.html
[@ハッシュ結合](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-46-2547814-ja.html
[@ハッシュ結合の拡張](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-41-2327818-ja.html
[@パーティション処理に関する実行計画](https://www.shift-the-oracle.com/performance-tuning/explain-plan-operation.html
[@ファクト表](https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_9.5.0/com.ibm.dwe.cubeserv.doc/topics/c_factanddimensiontables.html
@ファクト表
[@ブルーム・フィルタリング](https://www.oracle.com/technetwork/jp/database/articles/tsushima/ndex-1868009-ja.html
[@現在ログインしているユーザ名を確認する](http://d.hatena.ne.jp/yohei-a/20090630/1246326058
[@第8回オプティマイザ統計情報の管理～統計収集の高速化を体験してみる～](https://www.oracle.com/technetwork/jp/database/articles/shibacho/index-1714701-ja.html
[@索引スキャンのI/Oについて](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-34-2135613-ja.html
[@結合操作のヒント](https://docs.oracle.com/cd/E16338_01/server.112/b56312/hintsref.htm
[@結合操作ヒント](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-55-3227060-ja.html
[@結合操作ヒントについて](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-55-3227060-ja.html
[@結合順序のヒント](https://docs.oracle.com/cd/E16338_01/server.112/b56312/hintsref.htm
[@複数表ヒント](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-55-3227060-ja.html
[GATHER_OPTIMIZER_STATISTICS Hint](https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF56406
[Hints for Online Statistics Gathering for Bulk Loads](https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL345
[Oracle Hints Version 12.1.0.1](https://www.morganslibrary.org/reference/hints.html
[Traditional Joins ANSI Joins](https://www.morganslibrary.org/reference/joins.html#jansi
[](http://www.dba-oracle.com/t_materialize_sql_hint.htm
[](https://blogs.oracle.com/oracle4engineer/awrautomatic-workload-repository
[](https://dioncho.wordpress.com/2009/02/09/_with_subquery-parameter-controlling-subquery-factoring/
[](https://logmi.jp/tech/articles/283153
[](https://qiita.com/kentakanashi/items/61a43b359d219da66c89
[フィルタ述語とアクセス述語 (実行計画の読み方#3)](http://www.csus4.net/d/2011/06/30/predicate/
[実行計画のツリーのたどり方 (実行計画の読み方#1)](http://www.csus4.net/d/2011/06/20/traverse_plan_tree/
[実行計画の解析方法(1)](http://www.doppo1.net/oracle/tuning/execute-plan.html
[実行計画の解析方法(2)](http://www.doppo1.net/oracle/tuning/execute-plan2.html
[引用](http://www.doppo1.net/oracle/tuning/execute-plan.html
[第32回 SQL統計と実行計画の出力について](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-32-2047371-ja.html