ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL hint中正确使用use_nl提示

SQL hint中正确使用use_nl提示

原创 Linux操作系统 作者:star_guan2008 时间:2008-04-12 18:41:15 0 删除 编辑

之前对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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-19

  • 博文量
    82
  • 访问量
    107330