编者按：
本文作者系杨昱明，现就职于甲骨文公司，从事数据库方面的技术支持。希望能通过发表文章，把一些零散的知识再整理整理。个人主页:https://blog.csdn.net/weixin_50513167，经其本人授权发布。
Oracle SQL 性能调优：使用Hint固定执行计划1(Hash Join)
Nested Loop Join 指定时用到的 Hint
和 Hash Join 相对应的，通常，利用索引时一般会用到 Nested Loop Join。
下面我们来继续看看如何控制 Nested Loop Join 的使用，以及 Nested Loop Join 的顺序。
LEADING Hint （指定 Nested Loop Join 顺序）
USE_NL (指定使用 Nested Loop Join)
依然通过例子来进行说明。
准备：
代码语言：javascript复制drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);
create table t3(c1 number, c2 number);
insert into t1 values (1,1);
insert into t2 values (1,2);
insert into t3 values (1,3);
commit;由于没有见索引，所以结合时会用到 Hash Join。
代码语言：javascript复制SQL> select /* leading(b a) */ * from t1 a, t2 b where a.c1=b.c1;
C1 C2 C1 C2
---------- ---------- ---------- ----------
1 1 1 2
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 52 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."C1"="B"."C1")通过 USE_NL（内表 内表） Hint 来指定的话，就用到了 Nested Loop Join，Leading（外表->内表->内表） Hint 用来指定结合顺序。
代码语言：javascript复制SQL> select /* leading(b a) USE_NL(a) */ * from t1 a, t2 b where a.c1=b.c1;
C1 C2 C1 C2
---------- ---------- ---------- ----------
1 1 1 2
Execution Plan
----------------------------------------------------------
Plan hash value: 4016936828
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 52 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."C1"="B"."C1")上面是2个表的情况，那么3个表的情况呢。
代码语言：javascript复制SQL> select /* leading(a b c) USE_NL(b c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1;
C1 C2 C1 C2 C1 C2
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 2 1 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1998264463
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 78 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 52 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 26 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T2 | 1 | 26 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."C1"="B"."C1")
5 - filter("A"."C1"="C"."C1")
