SQL hint中正確使用use_nl提示

之前對use_nl的理解一直很模糊，看下面的案例。

SQL> select *
? 2? ? from table_detail t,
? 3? ? table(cast(str2varlist('123') as vartabletype)) t2
? 4? ?where t.id = t2.column_value;

Execution Plan
----------------------------------------------------------
Plan hash value: 31598426
----------------------------------------------------------
?Operation? ?| Name? ?|Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------
SELECT STATEMENT? ?|? ? ? |784K|124M|2132(1)|00:00:26|
?NESTED LOOPS? ? ? |? ? ? |784K|124M|2132(1)|00:00:26|
? TABLE ACCESS FULL | table_detail|96 |15744| 11(0)|00:00:01|
? COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST |? ?|? |? | |
-------------------------------------------------------------

t2是個(gè)虛擬的表，沒有準(zhǔn)確的統(tǒng)計(jì)信息，很容易出現(xiàn)執(zhí)行計(jì)劃錯(cuò)誤，這種sql最好要用hint來固化其執(zhí)行計(jì)劃。

1.使用/*+ ORDERED use_nl(t2,t) */提示

SQL> select /*+ ORDERED use_nl(t2,t) */*
? 2? ? from table_detail t,
? 3? ? table(cast(str2varlist('123') as vartabletype)) t2
? 4? ?where t.id = t2.column_value;

Execution Plan
----------------------------------------------------------
Plan hash value: 3775534098

----------------------------------------------------------------------------
?Operation? ? ? ? ? ? ? ? ? ? ? ? ?| Name? ? ? |Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
SELECT STATEMENT? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ? |784K|124M|2132(1)|00:00:26|
?NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? |784K|124M|2132(1)|00:00:26|
? TABLE ACCESS FULL? ? ? ? ? ? ? ? | table_detail|96 |15744| 11(0)|00:00:01|
? COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST |? ?|? ? ?|? ? ? |? ? ? ? |
----------------------------------------------------------------------------

執(zhí)行計(jì)劃是錯(cuò)誤的，還是取table_detail為驅(qū)動(dòng)表。
錯(cuò)誤理解：使用order提示，執(zhí)行計(jì)劃會(huì)去取use_nl(t2,t)中的t2作為驅(qū)動(dòng)表。
2.使用/*+ ORDERED use_nl(t,t2) */提示

SQL> select /*+ ORDERED use_nl(t,t2) */*
? 2? ? from table_detail t,
? 3? ? table(cast(str2varlist('123') as vartabletype)) t2
? 4? ?where t.id = t2.column_value;

Execution Plan
----------------------------------------------------------
Plan hash value: 3775534098

----------------------------------------------------------------------------
?Operation? ? ? ? ? ? ? ? ? ? ? ? ?| Name? ? ? |Rows |Bytes|Cost(%CPU)|Time|
----------------------------------------------------------------------------
SELECT STATEMENT? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ? |784K|124M|2132(1)|00:00:26|
?NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? |784K|124M|2132(1)|00:00:26|
? TABLE ACCESS FULL? ? ? ? ? ? ? ? | table_detail|96 |15744| 11(0)|00:00:01|
? COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST |? ?|? ? ?|? ? ? |? ? ? ? |
----------------------------------------------------------------------------

執(zhí)行計(jì)劃還是錯(cuò)誤的，這到底是怎么回事？
3.使用/*+ ORDERED use_nl(t,t2) */提示，同時(shí)調(diào)整了表順序。

SQL>? select /*+ ORDERED use_nl(t2,t) */*
? 2? ? from table(cast(str2varlist('123') as vartabletype)) t2,
? 3? ? ? ? ?table_detail t
? 4? ?where t.id = t2.column_value;

Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841

-------------------------------------------------------------------------------
?Operation? ? ? ? ? ? ? ? ? ? ? ? ? | Name? ? ? ?Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? |784K| 124M|25 (0)|00:00:01|
?TABLE ACCESS BY INDEX ROWID? ? ? ? | table_detail | 96 |15744| 1 (0)|00:00:01|
? NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? |784K| 124M|25 (0)|00:00:01|
?? COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST? |? ? |? ? ?|? ? ? |? ? ? ? |
?? INDEX RANGE SCAN? ? ? ? ? ? ? ? ?| IND_deail_id |? 1 |? ? ?| 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------

這回執(zhí)行計(jì)劃是正確的，原來order是根據(jù)from表順序來決定驅(qū)動(dòng)表，而不是use_nl(t,t2)中表的先后順序。
調(diào)整from后面的表順序是很老土的調(diào)優(yōu)，仿佛回到了rbo的年代，采用leading指定驅(qū)動(dòng)表看是否可行。
4.使用leading提示來指定驅(qū)動(dòng)表

SQL> select /*+ leading(t2) use_nl(t) */*
? 2? ? from table_detail t,
? 3? ? table(cast(str2varlist('123') as vartabletype)) t2
? 4? ?where t.id = t2.column_value;

Execution Plan
----------------------------------------------------------
Plan hash value: 2272521841
-------------------------------------------------------------------------------
?Operation? ? ? ? ? ? ? ? ? ? ? ? ? | Name? ? ? ?Rows |Bytes |Cost (%CPU)|Time|
-------------------------------------------------- ----------------------------
SELECT STATEMENT? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? |784K| 124M|25 (0)|00:00:01|
?TABLE ACCESS BY INDEX ROWID? ? ? ? | table_detail | 96 |15744| 1 (0)|00:00:01|
? NESTED LOOPS? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ? ? |784K| 124M|25 (0)|00:00:01|
?? COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST? |? ? |? ? ?|? ? ? |? ? ? ? |
?? INDEX RANGE SCAN? ? ? ? ? ? ? ? ?| IND_deail_id |? 1 |? ? ?| 1 (0)|00:00:01|
-------------------------------------------------- ----------------------------

1./*+use_nl(t2,t) */ 提示走nest loop,但沒有提示t2還是t為驅(qū)動(dòng)表 。
2./*+ ordered use_nl(t2,t) */提示走nest loop，order提示的是from后面的第一個(gè)表為驅(qū)動(dòng)表。
3./*+ leading(t2) use_nl(t) */ 直接提示t2為驅(qū)動(dòng)表。
結(jié)論：use_nl不能讓優(yōu)化器確定誰是驅(qū)動(dòng)表誰是被驅(qū)動(dòng)的表,use_nl(t,t2)也沒有指出哪個(gè)是驅(qū)動(dòng)表，這時(shí)候我們需要使用ordered,leading來強(qiáng)制指定驅(qū)動(dòng)表，以達(dá)到我們的目的。