hash join、nested loop，sort merge join(二)

SQL>

从中，可以看出在这种情况下hash join运行的速度远远大于nested loops；

什么时间optimizer 使用 hash joins呢？

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

1）A large amount of data must be joined.

2）A large fraction of a small table must be joined.

2、nested loop joins

这个东西叫嵌套循环链接，使用它的条件是

1）数据库获取的数据的集合不能太大

2）能够有效的访问内部表，也就是说内部表最好有索引。

看一下这句话：

It is important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

它包含的步骤如下：

1）optimizer 决定驱动表或是外部表

2）optimizer决定外部表

3）内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行

如果我们想强制使用nested loop joins可以使用use_nl(table1,table2)

eg：

SQL> set autotrace trace;

SQL> r

1* select empno,ename,job,mgr from amy_emp,amy_dept where amy_emp.deptno=amy_dept.deptno and amy_emp.ename in('KING','TURNER')

Elapsed: 00:00:00.05

Execution Plan

----------------------------------------------------------

Plan hash value: 2114037428

------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 56 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 2 | 56 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| AMY_EMP | 2 | 50 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | REVERSE_INDEX | 1 | 3 | 0 (0)| 00:00:01 |

-------------------------------------------

驱动表为amy_emp，使用use_nl转换驱动表；

注意：

use_nl(table1,table2)并不能让optimizer选择这两个表那个做为驱动表，如果想明确指定需要加ordered参数，对于ordered的参数指明from后边的表顺序为从左到右，也就是左边为驱动表，右边为被驱动表，另外如果use_nl参数仅仅指定一个表，那么这个表就为被驱动表,但指定的这个表为外部表那么则会忽略这个hint。

eg：

QL> select /*+use_nl(amy_emp,amy_dept)*/ empno,ename,job,mgr from amy_dept,amy_emp where amy_emp.deptno=amy_dept.deptno and amy_emp.ename in('KING','TURNER');

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 2114037428

------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 56 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 2 | 56 | 3 (0)| 00:00:01 |

|* 2 | TABLE ACCESS FULL| AMY_EMP | 2 | 50 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | REVERSE_INDEX | 1 | 3 | 0 (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("AMY_EMP"."ENAME"='KING' OR "AMY_EMP"."ENAME"='TURNER')

3 - access("AMY_EMP"."DEPTNO"="AMY_DEPT"."DEPTNO")

驱动表为：amy_emp,被驱动表为：amy_dept,use_nl无法判断。

SQL> select /*+use_nl(amy_emp)*/ empno,ename,job,mgr from amy_dept,amy_emp where amy_emp.deptno=amy_dept.deptno and amy_emp.ename in('KING','TURNER');

Elapsed: 00:00:00.00