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)，两表的
顺序无关紧要，系统自动选择小表作为驱动表。