摘自hellodba的Oracle的语句中的提示
对USE_NL有些疑问


USE_NL(table)
作用：使用Nested Loop方式进行连接。以指定的表为驱动表进行嵌套循环查询。Nested Loop对于嵌套查询一张大表和一张小表时比较有效，指定小表为驱动表。
例子：
SQL>select /*+use_nl(a)*/ a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=300 Bytes=11
          700)
   1    0   NESTED LOOPS (Cost=10 Card=300 Bytes=11700)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
          =1323 Bytes=34398)
   3    2       INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Ca
          rd=4764)
   4    1     INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)


执行顺序应该是:3-2-4-1-0
那么嵌套循环查询的驱动表应该是T_HUANG,而不是Hint中的T_WEI


对比：
SQL>select a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
执行顺序应该是:2-4-3-1-0
这个的嵌套循环查询的驱动表才是T_WEIT,而不是Hint中的T_HUANG


如有不对请大家指正!!!

1. You don't seem to have statistics collected.
2. When you add use_nl hint, it's better to add ordered (or leading) hint as well. Make sure the driving table is the first one in the from clause.

Yong Huang

what is ur oracle version?

是文档的说法有问题,可以用leading或者ordered来选择驱动表

首先谢谢各位大师！
    To  Yong Huang：
                1.statistics:
第一个语句的statistics
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        608  consistent gets
          0  physical reads
          0  redo size
      13072  bytes sent via SQL*Net to client
       1018  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        496  rows processed


第二个语句的statistics
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3069  consistent gets
          0  physical reads
          0  redo size
      13072  bytes sent via SQL*Net to client
       1018  bytes received via SQL*Net from client
         35  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        496  rows processed





To netbanker: my oracle version :  9.2.0.4.0


To 棉花糖ONE:文档的说法有问题指的是什么？－难道是指USE_NL(table)并不是讲该table作为驱动表吗？
最后还想问一下我判断的执行计划顺序是对的吗？因为这个执行计划好像跟作者的想法不是很一样。









[ 本帖最后由 iso90000 于 2008-4-22 15:39 编辑 ]

QUOTE:

---

原帖由 iso90000 于 2008-4-21 19:08 发表
首先谢谢各位大师！
    To  Yong Huang：
                1.statistics:
第一个语句的statistics
...

---

Table and index statistics please.

Also, try adding ordered hint along with use_nl.

Yong Huang

To  Yong Huang：
像use_nl(),use_hash()并不能让优化器确定谁是驱动表或谁是被驱动的表,需要使用orderded,LEADING才可以确定驱动表的顺序,这是我做完测试后的理解。

另外还有个问题：
在看Oracle9i Database Performance Tuning Guide and Reference的use_hash时对这句话不是太理解：
use_hash('table')
where 'table' is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
望大师给我一些解释，谢谢！

QUOTE:

---

原帖由 iso90000 于 2008-4-23 07:33 发表
To  Yong Huang：
像use_nl(),use_hash()并不能让优化器确定谁是驱动表或谁是被驱动的表,需要使用orderded,LEADING才可以确定驱动表的顺序,这是我做完测试后的理解。

另外还有个问题：
在看Oracle9i Database Performance Tuning Guide and Reference的use_hash时对这句话不是太理解：
use_hash('table')
where 'table' is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
望大师给我一些解释，谢谢！

---

Somebody knows better than I. When I use use_nl, I almost always use ordered at the same time. It's already a habit. Somebody intimately familiar with 10053 trace can answer the question why sometimes use_nl alone doesn't force Oracle to use the specified table to be the driving table.

9i documentation is not clear on that. I always thought use_hash('table') means the 'table' is the build table, i.e. the table whose joined columns are hashed into memory (or spilled to temp segment if hash area is too small).

Yong Huang

I always thought use_hash('table') means the 'table' is the build table, i.e. the table whose joined columns are hashed into memory (or spilled to temp segment if hash area is too small 我用个例子来表明我对这句话的理解

eg1：
select /*+use_hash(a)*/ * from a,b where a.id1=b.id1;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=409 Bytes=212
          68)
   1    0   HASH JOIN (Cost=5 Card=409 Bytes=21268)
   2    1     TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes=2132)
   3    1     TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=409 Bytes=10634)

use_hash(a)表明将表a在内存中根据连接键建立hash table，扫描表b并探测刚建成的hash table，找出与hash table匹配
的行（然后扫描表b，每读到一条记录就来探测hash表一次，找出与hash表匹配的行）。

执行顺序：扫描表b（驱动表）,并通过hash算法探测刚建成的hash table（由表a建立的），找出匹配的行

eg2:
      如果想已表a在内存中建立的hash table为驱动表
SQL> select /*+ordered use_hash(a)*/ * from a,b where a.id1=b.id1;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=409 Bytes=212
          68)
   1    0   HASH JOIN (Cost=5 Card=409 Bytes=21268)
   2    1     TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=409 Bytes=10634)
   3    1     TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=82 Bytes=2132)

这2种方式有区别吗？

ps:在eygle的9i Performance Tuning Guide 读书笔记一中有这样一句
  Execution Expain 中在上面的是先被扫描的小表，用作建立 hash table，在下的是大表。这好像与我想的eg1就有出入了，按照这个理解就是表b在内存中建立了hash table.

参考这个
http://rdc.taobao.com/blog/dba/html/114_oracle_use_nl.html