上文提到，两表关联返回少量数据应该走嵌套循环，两表关联返回大量数据应该走 HASH 连接。

    HASH 连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的「select 列和 join 列」读入 PGA 中的 work area，然后对驱动表的连接列进行 hash 运算生成 hash table，当驱动表的所有数据完全读入 PGA 中的 work area 之后，再读取被驱动表（被驱动表不需要读入 PGA 中的 work area），对被驱动表的连接列也进行 hash 运算，然后到 PGA 中的 work area 去探测 hash table，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。

    SGA(System Global Area)：由所有服务进程和后台进程共享； PGA(Program Global Area):由每个服务进程、后台进程专有

    我们在测试账号 scott 中运行如下 SQL。

    1. select /*+ gather_plan_statistics use_hash(e,d)  */
    2.   e.ename, e.job, d.dname
    3.    from emp e, dept d
    4.   where e.deptno = d.deptno;

    此处省略输出结果。

    我们运行如下命令获取执行计划。

    1. select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
    1. PLAN_TABLE_OUTPUT
    2. -------------------------------------------------------------------------------------
    3. SQL_ID  2dj5zrbcps5yu, child number 0
    4. -------------------------------------
    5. select /*+ gather_plan_statistics use_hash(e,d)  */  e.ename, e.job,
    6. d.dname   from emp e, dept d  where e.deptno = d.deptno
    8. Plan hash value: 615168685
    10. -------------------------------------------------------------------------------------
    11. | Id |Operation          |Name|Starts|E-Rows|A-Rows|   A-Time  |Buffers|OMem|1Mem|Used-Mem|
    12. -------------------------------------------------------------------------------------
    13. |  0 |SELECT STATEMENT   |    |     1|      |    14|00:00:00.01|     15|    |    |        |
    14. |* 1 | HASH JOIN         |    |     1|    15|    14|00:00:00.01|     15|888K|888K| 714K(0)|
    15. |  2 |  TABLE ACCESS FULL|DEPT|     1|     4|     4|00:00:00.01|      7|    |    |        |
    16. |  3 |  TABLE ACCESS FULL|EMP |     1|    15|    14|00:00:00.01|      8|    |    |        |
    17. -------------------------------------------------------------------------------------
    19. Predicate Information (identified by operation id):
    20. ---------------------------------------------------
    22.    1 - access("E"."DEPTNO"="D"."DEPTNO")

    执行计划中离 HASH 连接关键字最近的表就是驱动表。这里 DEPT 就是驱动表，EMP 就是被驱动表。驱动表 DEPT 只扫描了一次（Id=2，Starts=1），被驱动表 EMP 也只扫描了一次（Id=3，Starts=1）。再次强调，嵌套循环被驱动表需要扫描多次，HASH 连接的被驱动表只需要扫描一次。

    Used-Mem 表示 HASH 连接消耗了多少 PGA，当驱动表太大、PGA 不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘 HASH 连接，这时候 HASH 连接性能会严重下降。嵌套循环不需要消耗 PGA。

    嵌套循环每循环一次，会将驱动表连接列传值给被驱动表的连接列，也就是说嵌套循环会进行传值。HASH 连接没有传值的过程。在进行 HASH 连接的时候，被驱动表的连接列会生成 HASH 值，到 PGA 中去探测驱动表所生成的 hash table。HASH 连接的驱动表与被驱动表的连接列都不需要创建索引。

    OLTP 环境一般是高并发小事物居多，此类 SQL 返回结果很少，SQL 执行计划多以嵌套循环为主，因此 OLTP 环境 SGA 设置较大，PGA 设置较小（因为嵌套循环不消耗 PGA）。而 OLAP 环境多数 SQL 都是大规模的 ETL，此类 SQL 返回结果集很多，SQL 执行计划通常以 HASH 连接为主，往往要消耗大量 PGA，所以 OLAP 系统 PGA 设置较大。

    当两表使用外连接进行关联，如果执行计划走的是 HASH 连接，想要更改驱动表，我们需要使用 swap_join_inputs，而不是 leading，例如下面 SQL。

    1. explain plan for select /*+ use_hash(d,e) leading(e) */
    2.    *
    3.     from dept d
    4.    left join emp e on d.deptno = e.deptno;
    1. Explained.
    1. select * from table(dbms_xplan.display);
    1. PLAN_TABLE_OUTPUT
    2. ---------------------------------------------------------------------------
    3. Plan hash value: 3713469723
    5. ---------------------------------------------------------------------------
    6. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    7. ---------------------------------------------------------------------------
    8. |   0 | SELECT STATEMENT   |      |    14 |   812 |     7  (15)| 00:00:01 |
    9. |*  1 |  HASH JOIN OUTER   |      |    14 |   812 |     7  (15)| 00:00:01 |
    10. |   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
    11. |   3 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    12. ---------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
    19. 15 rows selected.

    从执行计划中我们可以看到，DEPT 与 EMP 是采用 HASH 连接，这说明 use_hash（d,e）生效了。执行计划中，驱动表为 DEPT，虽然设置了 leading（e），但是没有生效。现在我们使用 swap_join_inputs 来更改外连接中 HASH 连接的驱动表。

    1. explain plan for select /*+ use_hash(d,e) swap_join_inputs(e) */
    2.    *
    3.     from dept d
    4.     left join emp e on d.deptno = e.deptno;
    1. Explained.
    1. select * from table(dbms_xplan.display);
    1. PLAN_TABLE_OUTPUT
    2. ------------------------------------------------------------------------------
    3. Plan hash value: 3590956717
    5. ------------------------------------------------------------------------------
    6. | Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    7. ------------------------------------------------------------------------------
    8. |   0 | SELECT STATEMENT      |      |    14 |   812 |     7  (15)| 00:00:01 |
    9. |*  1 |  HASH JOIN RIGHT OUTER|      |    14 |   812 |     7  (15)| 00:00:01 |
    10. |   2 |   TABLE ACCESS FULL   | EMP  |    14 |   532 |     3   (0)| 00:00:01 |
    11. |   3 |   TABLE ACCESS FULL   | DEPT |     4 |    80 |     3   (0)| 00:00:01 |
    12. ------------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.    1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
    19. 15 rows selected.

    从执行计划中我们可以看到，使用 swap_join_inputs 更改了外连接中 HASH 连接的驱动表。

    思考：怎么优化 HASH 连接？

    回答：因为 HASH 连接需要将驱动表的 select 列和 join 列放入 PGA 中，所以，我们应该尽量避免书写 **select * from....**语句，将需要的列放在 select list 中，这样可以减少驱动表对 PGA 的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。如果无法避免驱动表被溢出到临时表空间，我们可以将临时表空间创建在 SSD 上或者 RAID 0 上，加快临时数据的交换速度。

    当 PGA 采用自动管理，单个进程的 work area 被限制在 1G 以内，如果是 PGA 采用手动管理，单个进程的 work area 不能超过 2GB。如果驱动表比较大，比如驱动表有 4GB，可以开启并行查询至少 parallel(4)，将表拆分为至少 4 份，这样每个并行进程中的 work area 能够容纳 1GB 数据，从而避免驱动表被溢出到临时表空间。如果驱动表非常大，比如有几十 GB，这时开启并行 HASH 也无能为力，这时，应该考虑对表进行拆分，在第 8 章中，我们会为大家详细介绍表的拆分方法。