sql oracle hint句とあそんでた

#まえがき

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

#ヒント句

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

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

<details><summary>sql（開く）</summary><div>

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

</div></details>

**#2.**sqlid取得

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

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

<details><summary>sql（開く）</summary><div>

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

</div></details>

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

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

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

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>


##GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS

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

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-----------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------
```

</div></details>

**コメント**

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

####ヒントあり

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------
| 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
 
```

</div></details>

**コメント**

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

##GATHER_PLAN_STATISTICS 

以下のsqlを実行する代わりに、対象の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

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

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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`と呼んでいるらしい。

####ヒントあり

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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`で試してみた。結合してからサマル場合と比較してみた。もはやヒント関係ないけど。

####ヒントなし

結合してからサマル版。

<details><summary>sql（開く）</summary><div>

```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;
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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`して左相関させる版。

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

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


**コメント**

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

##NO_INDEX MATERIALIZE INLINE

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

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

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------
| 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)

```

</div></details>

**マッピング表**

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

**コメント**

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

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

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

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------
| 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)
```

</div></details>


**マッピング表**

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

**コメント**

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


###サンプル②

####ヒントなし


<details><summary>sql（開く）</summary><div>

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

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------
| 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)
```

</div></details>

**マッピング表**

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

**コメント**

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


####ヒントあり


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

<details><summary>sql（開く）</summary><div>

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

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------
| 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)
```

</div></details>

**マッピング表**

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


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

###サンプル③

####ヒントなし

<details><summary>sql（開く）</summary><div>

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

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------
| 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)
```

</div></details>

**マッピング表**

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

**コメント**

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


####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------
| 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)
```

</div></details>


**オブジェクト別名**

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

**マッピング表**

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


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

###サンプル④

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>


**マッピング表**

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

**コメント**

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

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
----------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>


**マッピング表**

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

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

###サンプル⑤

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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_MST`1回ループにつき、内部表`CUST_MST`フルスキャンで3行取得なので、1×3=3行取得できる。そのあと、結合列(id=1のaccess述語で分かる)にHASH関数がかまされ、PGA内にハッシュ表が作成される。`SALES_TRN`のfilter条件のふるいにかけられて取得できた540行の結合列(id=1のaccess述語で分かる)を順にHASH関数にかけ、先にPGAへ読みこんだハッシュ表の結合列と照合を実施し、結合条件に合致する行を特定する。

####ヒントあり

>`/*+ MATERIALIZE */`

<details><summary>sql（開く）</summary><div>
```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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`）をメモリ内で作成していることを示しているとおもう。

###サンプル⑥

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

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

**コメント**

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

####ヒントあり

>`/*+ INLINE */`

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

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

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

##NO_MERGE

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

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>


###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

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

**コメント**

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

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
----------------------------------------------------------------------------------------------------------------------
| 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'))

```

</div></details>

**マッピング表**

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


**コメント**

>`SALES_TRN_ITEM_MST`Viewが生成されてから`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

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**オブジェクト別名**

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

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>


```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

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

>**#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`にテーブルアクセスする。


###サンプル②

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>



**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>


```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>


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

>**#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`にテーブルアクセスする。

###サンプル③

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------
| 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')
```


</div></details>


**オブジェクト別名**

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

**マッピング表**

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



**コメント**

>実行計画が右下に行くにつれてインデントが深くなるのは`right deep join`の傾向だな。ファクト表をProbeするよりもマスタをProbeしていくほうがたしかに効率よさそうだな。ハッシュ結合って結合対象の表のうち小さい表から読み込んでハッシュ表をPGAに作るんだ。`CUST_MST`3行、`ITEM_MST`13行、`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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
----------------------------------------------------------------------------------------------------------------------
| 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")
```


</div></details>

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

>**#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_`がつくヒント全般にいえるか。。

###サンプル④

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`


<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>


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

>**#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ヒント`をヒントなしで使ってます。


<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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]); */`

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------
| 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")
```

</div></details>


**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------
| 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")
```


</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>


```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

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

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------
| 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"))
```

</div></details>

**オブジェクト別名**

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

**マッピング表**

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

**コメント**
>`MERGE JOIN`。。

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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;
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

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

**コメント**
>`USE_NL_WITH_INDEX`ヒントいいかんじやな。

##LEADING USE_HASH

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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;
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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が工夫して結合しているのであろう。


####ヒントあり

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>


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

>**#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|


**コメント**

>ねらいどおりきまった。


###サンプル②

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>



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

>**#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|

**コメント**

>狙い通り。


###サンプル③

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------
| 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')
```


</div></details>


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

>**#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。


###サンプル④

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-----------------------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```


</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

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

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

**マッピング表**

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

**コメント**

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

###サンプル⑤

####ヒントなし

<details><summary>sql（開く）</summary><div>


```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';
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-----------------------------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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]); */`

<details><summary>sql（開く）</summary><div>


```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

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

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

**マッピング表**

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

**コメント**

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

##LEADING USE_MERGE

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```
</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```


</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';

```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql

---------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>

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

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

**マッピング表**

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

**コメント**

>ねらいどおり。


###サンプル②

####ヒントなし

<details><summary>sql（開く）</summary><div>


```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```


</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>


<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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

```

</div></details>

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

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


**マッピング表**

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


**コメント**

>ねらいどおり。


###サンプル③

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```
</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------------
| 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'))
```
</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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>) */`

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
--------------------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>


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

>**#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

###サンプル①

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-----------------------------------------------------------------------------------------------------
| 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

```
</div></details>

**マッピング表**

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

**コメント**
>`NL`がない！！select句に書くと。おぉー。

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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';
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------
| 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'))
```

</div></details>

**マッピング表**

| オブジェクト別名 | テーブル名 |
|:-:|:-:|
| 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`になった！！

###サンプル②

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------
| 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")

```

</div></details>

**マッピング表**

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

**コメント**
>`NL`うまれた。

####ヒントあり

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

<details><summary>sql（開く）</summary><div>

```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
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------
| 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)
```
</div></details>


**マッピング表**

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

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



###サンプル③

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
-------------------------------------------------------------------------------------------------------
| 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')
```

</div></details>

**マッピング表**

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

**コメント**
>`NL ANTI`うまれた。

####ヒントあり

<details><summary>sql（開く）</summary><div>

```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
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------
| 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)
```

</div></details>

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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するということだろう。きっと。

###サンプル④

####ヒントなし

<details><summary>sql（開く）</summary><div>

```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
			)
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
---------------------------------------------------------------------------------------------------------------------------------
| 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")
```

</div></details>


**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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`でた！！

####ヒントあり

<details><summary>sql（開く）</summary><div>

```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
			)
	);
```

</div></details>

<details><summary>実行計画（開く）</summary><div>

```sql
------------------------------------------------------------------------------------------------------------------------------
| 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)
```

</div></details>

**マッピング表**

| オブジェクト別名 | オブジェクト名 |
|:-:|:-:|
| 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について](https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-46-2547814-ja.html)
>[@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](https://www.morganslibrary.org/reference/partitions.html)
>[@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.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