对USE_NL这个hint的一些疑问

iso90000

总结一下：
  在nested loop中：以小表作为驱动表，去找大表中符合的记录
在hash jion中：以小表做为hash table,大表为探查表（驱动表），去hash table(小表)中找符合的记录

oldmen2007

实验如下:
SYS@sid33>select /*+ ordered use_nl(t1 t2 t3) */ * from t1,t2,t3
  2  where t1.object_id = t2.object_id and t2.object_id = t3.object_id;

9892 rows selected.

Elapsed: 00:00:33.59

Execution Plan
----------------------------------------------------------
Plan hash value: 1998264463

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  8921 |  4626K|   570K  (1)| 01:54:05 |
|   1 |  NESTED LOOPS       |      |  8921 |  4626K|   570K  (1)| 01:54:05 |
|   2 |   NESTED LOOPS      |      | 10409 |  3598K|   285K  (2)| 00:57:05 |
|   3 |    TABLE ACCESS FULL| T1   | 10408 |  1799K|    29   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |   177 |    27   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | T3   |     1 |   177 |    27   (0)| 00:00:01 |
----------------------------------------------------------------------------

SYS@sid33>select /*+ ordered use_hash(t1 t2 t3) */ * from t1,t2,t3
  2  where t1.object_id = t2.object_id and t2.object_id = t3.object_id;

9892 rows selected.

Elapsed: 00:00:00.42

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526

--------------------------------------------------------------------------------
----
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
   |
--------------------------------------------------------------------------------
----
|   0 | SELECT STATEMENT    |      |  8921 |  4626K|       |   541   (1)| 00:00:
07 |
|*  1 |  HASH JOIN          |      |  8921 |  4626K|  1680K|   541   (1)| 00:00:
07 |
|   2 |   TABLE ACCESS FULL | T3   |  9091 |  1571K|       |    29   (0)| 00:00:
01 |
|*  3 |   HASH JOIN         |      | 10409 |  3598K|  1928K|   248   (1)| 00:00:
03 |
|   4 |    TABLE ACCESS FULL| T1   | 10408 |  1799K|       |    29   (0)| 00:00:
01 |
|   5 |    TABLE ACCESS FULL| T2   | 10609 |  1833K|       |    29   (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----

看来还是不统一:
1. NL时候: 完全符合 (1->2)->3 的顺序, 没问题
2. Hash时候: 按照前面各位讨论的结论应该是 3<-(2<-1) 才对. 而现在是3<-(1<-2)

宇野

加上use_nl(a) hint,指定一个表名或别名是指定inner table ,而不是指定驱动表.
oracle 选不选择nl,还得看成本计算结果.

[ 本帖最后由 宇野 于 2008-6-29 20:31 编辑 ]

sqysl

是的，楼上说的有道理，楼主一开始提出的问题就是对正确的结果提出了错误的问题，你的执行计划的读取顺序完全正确，可是USE_NL（a）只是强调了两点：第一，要用NL连接方式；第二，以a为内部表而不是驱动表，其实，楼主的加USE_NL 的结果就是以a为内部表的，而下面没加USE_NL的结果是系统自动选择了a表作为驱动表而不是内部表，仅此而已。如果仅仅用这个HINT，有时并不一定能保证连接的顺序，而如果保证连接顺序，最好使用ORDERED或LEADING HINT，就算使用这两个HINT也不能百分百的保证语句执行时完整按照人们想的顺序去执行。对于USE_NL和USE_MERGE，ORDERED和LEADING一般能保证连接顺序，而对于USE_HASH，似乎并不能保证，ORACLE文档里也没提到，其实，对于HASH JION也确实没必要一定规定连接顺序，一起讨论。

[ 本帖最后由 sqysl 于 2008-6-29 21:34 编辑 ]

eagle_fan

原帖由 Yong Huang 于 2008-4-28 12:51 发表


I think I know now. I was wrong in my understanding. iso90000 and ZALBB are correct. This comment by Jonathan Lewis is also interesting:
http://jonathanlewis.wordpress.com/2007/01/16/full-hinting/
"the use_hash(tabX) hint tells the optimizer to use a hash join to join this table to the preceding result set. It doesn’t dictate the order at all. The swap_join_inputs(tabX) forces tabX to be the build (first) table in the join, the 10g hint no_swap_join_inputs(tabX) forces tabX to be the probe (second) table."

So why did you get the above plan when you have ordered and use_hash and tables are t_max, t_middle and t_small in that order? Here, t_max is first hashed into memory (build table), as dictated by the ordered hint. t_middle is the driving i.e. probe table (each row of it is taken out, compute its hash value, check the in-memory hash table built earlier for t_max to find a match). This result is used to do a hash join with t_small. Now here's the tricky part. Since t_small (line 3) is above, not below, line 4, it's the build table for this hash join (line 2). I think the normal reading order of the plan is not quite appropriate here. In reality, I'm not sure if Oracle already starts to build t_small's hash cluster in memory or not when it's working on lines 5 and 6.

What if you add swap_join_inputs(t_small)? If the plan stays the same as above, it just guarantees this plan's stability. If t_small drops to the bottom, I'll be surprised.

Yong Huang

Interesting. I didn't find the two hints in oracle 10g documentation. I did some tests on 10.2.0.3 & 11.1.0.6 versions, seems swap_join_inputs works well but  no_swap_join_inputs doesn't work.

eagle_fan

post my screen:

version 10.2.0.3

SQL> create table t(x int);
c
Table created.

SQL> reate table t2(x int);

Table created.

SQL> set autotrace on
SQL> select  /*+ use_hash(t,t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |  2000 | 26000 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  2000 | 26000 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")


Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        318  bytes sent via SQL*Net to client
        477  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ use_hash(t,t2) no_swap_join_inputs(t)  */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |  2000 | 26000 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  2000 | 26000 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     0
optimizer_features_enable            string      10.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      RULE
optimizer_secure_view_merging        boolean     TRUE
SQL>
SQL> select /*+ use_hash(t,t2) swap_join_inputs(t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3648734886

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 52000 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |  2000 | 26000 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |  2000 | 26000 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

eagle_fan

version : 11.1.0.6

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.1.0.6
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      rule
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> set autotrace on      
SQL> select  /*+ use_hash(t,t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ use_hash(t,t2) no_swap_join_inputs(t)  */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ use_hash(t,t2) swap_join_inputs(t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3648734886

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

owlstudio

嗯，有点收获

battleman

原帖由 eagle_fan 于 2008-6-30 11:49 发表
version : 11.1.0.6

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.1.0.6
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      rule
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> set autotrace on      
SQL> select  /*+ use_hash(t,t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ use_hash(t,t2) no_swap_join_inputs(t)  */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1252619702

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ use_hash(t,t2) swap_join_inputs(t2) */ t.x from t,t2 where t.x=t2.x;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3648734886

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    26 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."X"="T2"."X")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

no_swap_join_inputs和swap_join_inputs应该是针对2个以上表hash连接，用来控制结果集join的。
你这种情况swap_join_inputs之所以起作用，是因为你测试所采用的两张表大小完全一样，所以oracle 采用了你的提示。
如果你使用一大一小两张表来测试，oracle会忽略swap_join_inputs提示，而采用cost来选择内表

Yong Huang

原帖由 battleman 于 2009-1-5 20:21 发表

你这种情况swap_join_inputs之所以起作用，是因为你测试所采用的两张表大小完全一样，所以oracle 采用了你的提示。
如果你使用一大一小两张表来测试，oracle会忽略swap_join_inputs提示，而采用cost来选择内表

Doesn't sound right. Can you show us a test case? And remember to tell us version.

Yong Huang