SQL hint中正確使用use_nl提示

star_guan2008發表於2008-04-12
SQL

之前對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是個虛擬的表，沒有準確的統計資訊，很容易出現執行計劃錯誤，這種sql最好要用hint來固化其執行計劃。

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

執行計劃是錯誤的，還是取table_detail為驅動表。
錯誤理解：使用order提示，執行計劃會去取use_nl(t2,t)中的t2作為驅動表。
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 |   |     |      |        |
----------------------------------------------------------------------------

執行計劃還是錯誤的，這到底是怎麼回事？
3.使用/*+ ORDERED use_nl(t,t2) */提示，同時調整了表順序。

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

這回執行計劃是正確的，原來order是根據from表順序來決定驅動表，而不是use_nl(t,t2)中表的先後順序。
調整from後面的表順序是很老土的調優，彷彿回到了rbo的年代，採用leading指定驅動表看是否可行。
4.使用leading提示來指定驅動表

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為驅動表 。
2./*+ ordered use_nl(t2,t) */提示走nest loop，order提示的是from後面的第一個表為驅動表。
3./*+ leading(t2) use_nl(t) */ 直接提示t2為驅動表。
結論：use_nl不能讓優化器確定誰是驅動表誰是被驅動的表,use_nl(t,t2)也沒有指出哪個是驅動表，這時候我們需要使用ordered,leading來強制指定驅動表，以達到我們的目的。

<!--

--EOF--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10742223/viewspace-235563/，如需轉載，請註明出處，否則將追究法律責任。

相關文章