Oracle 表连接类型及方式详解

1 概述

1. 概念
   表连接类型：咱写 sql 语句时，表之间的关联关系
   表连接方式：执行计划里面的表连接方式
   
2. 表连接类型 -- 等值连接
   (1) 内连接  inner join，简写 join
   (2) 左连接  left  join
   (3) 右连接  right join
   (4) 全连接  full  join
   
3. 表连接方式
   (1) 嵌套循环    nested loops
   (2) 哈希连接    hash join
   (3) 排序合并连接 sort merge join
   (4) 笛卡尔积    cartesian product

2 表连接类型

红色：表连接后的结果集

写法1：标准 SQL 语法

with a as (
  select 1 id, '瑶瑶' name from dual union all
  select 2 id, '优优' name from dual union all
  select 3 id, '倩倩' name from dual
), b as (
  select 1 id, '女'   sex from dual union all
  select 2 id, '男'   sex from dual union all
  select 9 id, '未知' sex from dual
) 
select a.*, b.*
  from a 
  left join b on a.id = b.id;

写法2：老油条，表和表位置很清楚。(+) 一侧，表示左连接

with a as (
  select 1 id, '瑶瑶' name from dual union all
  select 2 id, '优优' name from dual union all
  select 3 id, '倩倩' name from dual
), b as (
  select 1 id, '女'   sex from dual union all
  select 2 id, '男'   sex from dual union all
  select 9 id, '未知' sex from dual
) 
select a.*, b.*
  from a,
       b
 where a.id = b.id(+);

3 表连接方式

1. 概念
   Driving Table（驱动表）：执行计划最先执行的那个表 /*+ leading(t1)*/
   Probed Table（匹配表）：与驱动表进行连接的表     

2. 三种连接方式 hint
   nested loops /*+ use_nl(t2)*/
   hash join    /*+ use_hash(t2)*/
   merge        /*+ user_merge(t2)*/

表连接方式	访问次数	适应场景	限制场景	索引使用
嵌套循环	驱动表返回几条，被驱动表访问多少次	小量数据的连接或小表大表连接	无任何限制	驱动表的限制条件和被驱动表的连接条件上创建索引
哈希连接	表都最多访问 1 次	大数据的连接或小表大表连接	只能使用 =	索引列无要求，与单表情况无异
排序合并		大数据的连接且有排序操作	无任何限制	索引可以消除排序

3.1 nested loops

1. 工作原理
   驱动表 t1，有 m 条记录
   匹配表 t2，有 n 条记录

   for i in 1 .. m loop
     for j in 1 .. n loop
       if t1[i].col = t2[j].col then
          匹配成功;
       end if;
     end loop;  
   end loop; 

2. 结论
   (1) T1 表每返回一条记录，都要去 T2 表去轮询一次，得到与其匹配的数据，推送到结果集中
       所以，在嵌套表循环的使用中，必须设置 '返回记录少' 的表作为驱动表
       
   (2) 可以通过以下两点来提高嵌套循环的速度
       第一：尽量提高 T1 表 '取'  记录的速度(T1 表的连接列上创建索引)
       第二：尽量提高 T2 表 '匹配' 记录的速度(T2 表的连接列上创建索引)

3. 示例
   scott.emp  -- 14 条记录
   scott.dept -- 4 条记录

情况1：以 scott.dept（小表） 为驱动表，执行计划如下：

SQL> alter session set statistics_level = all;

SQL> select /*+ leading(d) use_nl(e)*/
            e.*,
            d.*
       from scott.emp  e,
            scott.dept d
      where d.deptno = e.deptno;

SQL> select t.*
       from v$sql t
      where t.sql_text like '%FROM scott.emp  e,%'
      order by t.last_active_time desc;
 
SQL> select * from table(dbms_xplan.display_cursor('c2ckftf2gj7qv',null,'allstats last')); 

打印结果：

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.02 |      35 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.02 |      35 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.02 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      28 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."DEPTNO"="E"."DEPTNO")

解释说明：

1. 首先，读取 id = 2 的记录，如下：
   读取表 DEPT，读取次数 Starts = 1，实际读取记录数 A-Rows = 4 条

2. 其次，读取 id = 3 的记录，如下：
   读取表 EMP，读取次数 Starts = 4，实际读取记录数（每次） A-Rows = 14 条 

3. 然后，读取 id = 1，将两表进行 '嵌套循环'

4. 最后，读取 id = 0，查询最终结果

情况2：同理，以 scott.emp（“大表”） 为驱动表，Starts = 14（id=4），变多了：

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |     14 |00:00:00.01 |      25 |
|   1 |  NESTED LOOPS                |         |      1 |        |     14 |00:00:00.01 |      25 |
|   2 |   NESTED LOOPS               |         |      1 |     14 |     14 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS FULL         | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |       4 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |      14 |
--------------------------------------------------------------------------------------------------

3.2 hash join

SELECT /*+ leading(e) use_hash(d)*/
 e.*,
 d.*
  FROM scott.emp  e,
       scott.dept d
 WHERE d.deptno = e.deptno;

执行计划如下：

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      14 |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      14 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
|   3 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------

3.3 merge

SELECT /*+ leading(e) use_merge(d)*/
 e.*,
 d.*
  FROM scott.emp  e,
       scott.dept d
 WHERE d.deptno = e.deptno;

执行计划如下：

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     14 |00:00:00.01 |      14 |
|   1 |  MERGE JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      14 |
|   2 |   SORT JOIN         |      |      1 |     14 |     14 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
|*  4 |   SORT JOIN         |      |     14 |      4 |     14 |00:00:00.01 |       7 |
|   5 |    TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------