表关联查询的几个hint用法

1，use_nl的用法

select e.ename,d.dname,e.job
from dept d,emp e
where e.deptno=d.deptno;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |     5 |
|*  1 |  HASH JOIN           |             |    14 |   392 |     5 |
|   2 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
|   3 |   TABLE ACCESS FULL  | EMP         |    14 |   238 |     2 |
--------------------------------------------------------------------

select /*+ use_nl(e) */e.ename,d.dname,e.job
from dept d,emp e
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |     5 |
|*  1 |  HASH JOIN           |             |    14 |   392 |     5 |
|   2 |   TABLE ACCESS FULL  | EMP         |    14 |   238 |     2 |
|   3 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
--------------------------------------------------------------------

select /*+ ordered use_nl(e) */e.ename,d.dname,e.job
from dept d,emp e
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |    12 |
|   1 |  NESTED LOOPS        |             |    14 |   392 |    12 |
|   2 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
|*  3 |   TABLE ACCESS FULL  | EMP         |     3 |    51 |     2 |
--------------------------------------------------------------------

select /*+ use_nl(d) */e.ename,d.dname,e.job
from dept d,emp e
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |     5 |
|*  1 |  HASH JOIN           |             |    14 |   392 |     5 |
|   2 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
|   3 |   TABLE ACCESS FULL  | EMP         |    14 |   238 |     2 |
--------------------------------------------------------------------

select /*+ ordered use_nl(d) */e.ename,d.dname,e.job
from emp e,dept d
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |    30 |
|   1 |  NESTED LOOPS        |             |    14 |   392 |    30 |
|   2 |   TABLE ACCESS FULL  | EMP         |    14 |   238 |     2 |
|*  3 |   TABLE ACCESS FULL  | DEPT        |     1 |    11 |     2 |
--------------------------------------------------------------------

select /*+ ordered use_nl(e) */e.ename,d.dname,e.job
from emp e,dept d
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |     5 |
|*  1 |  HASH JOIN           |             |    14 |   392 |     5 |
|   2 |   TABLE ACCESS FULL  | EMP         |    14 |   238 |     2 |
|   3 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
--------------------------------------------------------------------

select /*+ use_nl(d,e) */e.ename,d.dname,e.job
from emp e,dept d
where e.deptno=d.deptno;

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |    12 |
|   1 |  NESTED LOOPS        |             |    14 |   392 |    12 |
|   2 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
|*  3 |   TABLE ACCESS FULL  | EMP         |     3 |    51 |     2 |
--------------------------------------------------------------------

select /*+ use_nl(e,d) */e.ename,d.dname,e.job
from emp e,dept d
where e.deptno=d.deptno;
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    14 |   392 |    12 |
|   1 |  NESTED LOOPS        |             |    14 |   392 |    12 |
|   2 |   TABLE ACCESS FULL  | DEPT        |     5 |    55 |     2 |
|*  3 |   TABLE ACCESS FULL  | EMP         |     3 |    51 |     2 |
--------------------------------------------------------------------

结论：在使用use_nl时，最好和ordered同时使用，这时在from子句中排列在前的表为驱动表；
或者不用ordered，但 在use_nl中将进行嵌套循环连接的两表列出，例如use_nl(e,d)，两表的
顺序无关紧要，系统自动选择小表作为驱动表。

1./*+use_nl(t2,t) */ 仅仅提示oracle走nest loop，没有指定任何驱动表
2./*+ ordered use_nl(t2,t) */走nest loop，order指定以from后面的第一个表做为驱动表。
3./*+ leading(t2) use_nl(t) */ 走nest loop，提示t2为驱动表。
结论：use_nl不能让优化器确定谁是驱动表谁是被驱动的表,use_nl(t,t2)也没有指出哪个是驱动表，这 时候我们需要使用ordered,leading来强制指定驱动表，以达到我们的目的。

多表之间的连接有三种方式： Nested Loops ， Hash Join 和 Sort Merge Join. 下面来介绍三种不同连接的不同：

 

 

一． NESTED LOOP:

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择 。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于 1 万不适合），要把返回子集较小表的作为外表（ CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用 ORDERED 提示来改变 CBO 默认的驱动表，使用 USE_NL(table_name1 table_name2) 可是强制 CBO 执行嵌套循环连接 。

        

Nested loop 一般用在连接的表中有索引，并且索引选择性较好的时候 .

 

步骤： 确定一个驱动表 (outer table) ，另一个表为 inner table ，驱动表中的每一行与 inner 表中的相应记录 JOIN 。类似一个嵌套的循环。适用于驱动表的记录集比较小（ <10000 ）而且 inner 表需要有有效的访问方法（ Index ） 。需要注意的是： JOIN 的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

cost = outer access cost + (inner access cost * outer cardinality)

 

| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
| 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
| 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |

EMPLOYEES 为 outer table, JOBS 为 inner table.

 

 

二． HASH JOIN :

散列连接是 CBO 做大数据集连接时的常用方式 ，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高 I/O 的性能。

也可以用 USE_HASH(table_name1 table_name2) 提示来强制使用散列连接 。如果使用散列连接 HASH_AREA_SIZE 初始化参数必须足够的大，如果是 9i ， Oracle 建议使用 SQL 工作区自动管理，设置 WORKAREA_SIZE_POLICY 为 AUTO ，然后调整 PGA_AGGREGATE_TARGET 即可。

        

Hash join 在两个表的数据量差别很大的时候 .

 

步骤： 将两个表中较小的一个在内存中构造一个 HASH 表（对 JOIN KEY ），扫描另一个表，同样对 JOIN KEY 进行 HASH 后探测是否可以 JOIN 。适用于记录集比较大的情况。 需要注意的是：如果 HASH 表太大，无法一次构造在内存中，则分成若干个 partition ，写入磁盘的 temporary segment ，则会多一个写的代价，会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost

 

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
| 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------

ORDERS 为 HASH TABLE ， ORDER_ITEMS 扫描

 

 

三． SORT MERGE JOIN

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用 USE_MERGE(table_name1 table_name2) 来强制使用排序合并连接 .

        

Sort Merge join 用在没有索引，并且数据已经排序的情况 .

 

cost = (outer access cost * # of hash partitions) + inner access cost

 

步骤： 将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种 JOIN 方式：

1.RBO 模式

2. 不等价关联 (>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4. 数据源已排序

 

 

四.  三种连接工作方式比较：

     

       Hash join 的工作方式 是将一个表（通常是小一点的那个表）做 hash 运算，将列数据存储到 hash 列表中，从另一个表中抽取记录，做 hash 运算，到 hash 列表中找到相应的值，做匹配。

        

Nested loops 工作方式 是从一张表中读取数据，访问另一张表（通常是索引）来做匹配， nested loops 适用的场合是当一个关联表比较小的时候，效率会更高。

 

         Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为 merge join 需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用 merge join 的地方， hash join 都可以发挥更好的性能。