本文讨论一下join技术背后的机制。我们知道常用的表连接有如下几种

网站建设哪家好，找创新互联！专注于网页设计、网站建设、微信开发、小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了秀屿免费建站欢迎大家使用！

• 笛卡尔连接

• 内连接

• 左外连接

• 右外连接

• 全连接

这些sql的写法，想必大家都很清楚了，那么这些连接的数据访问是如何实现的呢？

• nested loop

我们看如下查询

SQL> alter session set optimizer_mode=rule; Session altered. SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3625962092 ------------------------------------------------ | Id  | Operation      | Name    | ------------------------------------------------ |   0 | SELECT STATEMENT      |        | |   1 |  NESTED LOOPS      |        | |   2 |   NESTED LOOPS      |        | |   3 |    TABLE ACCESS FULL      | EMP     | |*  4 |    INDEX UNIQUE SCAN      | PK_DEPT | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    | ------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

根据我们之前讲的执行计划解读，本查询是这样实现的：

1. 全表扫描emp表（非阻塞扫描，并不是将数据全部取出，才执行下一步）。

2. 将emp中的数据逐条取出，通过索引PK_DEPT查询出索引中的rowid，结果集变成（ename，rowid）

3. 将2生成的结果集逐条取出，通过rowid去访问dept表，结果集变成（ename，dname）

4. 将结果集返回。

这种以循环的方式取出数据的join实现方式就叫嵌套循环。

此计划可以用如下逻辑伪代码实现

for y in （for x in （select * from emp）loop          index lookup the rowid for x.deptno          output joined record（ename，dept.rowid）        end loop）loop  select * from dept where rowid=y.rowid  output joined record(ename,dname) end loop

我们把emp表称之为驱动表（注驱动表与from子句的表顺序无关，主要看执行计划）。

此种连接方式，适用于驱动表返回数据比较少，并且被驱动表dept上deptno列有索引。如果查询返回n行，那么dept表将被扫描n次。此连接擅长于从结果集中迅速取出第一行。

• Hash Join

 Hash Join适合处理大型结果集，优化器选择两个表或者源数据中比较小的，使用join key在内存中建立一个hash table。然后扫描大表，并探查hash表，去发现匹配的记录。
 小表称为驱动表，大表称为探查表

当hash table能全部放到内存中，此种情况最好。如果内存中放不下hash table，优化器将hash table分区，超出内存范围的分区将被写到临时表空间中。

我们分两种情况讨论hash join的实现

1. hash table 全部在内存里

 hash table是Oracle根据join key利用一个hash函数将小表分割成多个bucket。hash table建立完成后，Oracle去扫描大表，并且采用相同的hash算法，将读入的数据也分割成多个bucket。bucket与bucket之间进行join运算，返回结果。直到大表读完为止。

2. hash table 不能全部放到内存中

 这种情况有点麻烦，当Oracle发现内存无法完全存放小表，Oracle在构造hash table时，将小表进行分区，每个分区中再构造bucket 。当内存写满时，Oracle将内存中大的分区写到tempfile中。用这个方法，直到小表hash table构造完成。此时，hashtable一部分数据在内存，一部分数据在tempfile。

 当Oracle去扫描大表时，如果扫描的行通过hash在内存中能找到结果，就匹配成功。如果不能命中，则采用与hash table相同的分区方式，先将数据写入tempfile中。当大表全部扫描完毕，hash table内存中的部分已全部匹配完。此时依次将tempfile中的分区加载到内存中。重新扫描大表临时存在tempfile中的相应分区加以匹配。直到数据全部处理完。

SQL> insert into big_emp select * from big_emp; SQL> insert into big_emp select * from big_emp;#重复执行多次 SQL> / 458752 rows created. SQL> create table dept_new as select * from dept; Table created. SQL> set autot traceonly SQL> select * from big_emp a,dept_new b where a.deptno=b.deptno; 917504 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1925493178 ------------------------------------------------------------------------------- | Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |   917K|    54M|  1490 (2)| 00:00:18 | |*  1 |  HASH JOIN    |       |   917K|    54M|  1490 (2)| 00:00:18 | |   2 |   TABLE ACCESS FULL| DEPT_NEW |     4 |   120 |     3 (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| BIG_EMP  |   917K|    28M|  1482 (1)| 00:00:18 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("A"."DEPTNO"="B"."DEPTNO") Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------   4  recursive calls   1  db block gets       66338  consistent gets   0  physical reads   0  redo size    62512398  bytes sent via SQL*Net to client      673349  bytes received via SQL*Net from client       61168  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)      917504  rows processed

• Sort Merge Joins

  排序合并连接与嵌套循环和散列连接都不同，排序合并连接没有驱动表的概念。简言之，排序合并将依次处理排序第一个输入集，排序第二个输入集，然后合并结果。排序合并通常不如散列高效，因为两个结果集都需要排序，而散列连接在数据输出前，只需处理一个结果集。排序合并通常在非等值连接中有效。即连接条件不是一个等式而是范围比较（<或者>=）. 或者是两个表的数据已经排好序啦。

我们看如下例子

SQL> set linesize 200 pagesize 200 SQL> set autot traceonly SQL> select a.ename,b.ename,a.hiredate,b.hiredate   2  from emp a,emp b   3  where a.empno<>b.empno and a.hiredate"B"."EMPNO")    5 - access("A"."HIREDATE"<"B"."HIREDATE")        filter("A"."HIREDATE"<"B"."HIREDATE") Statistics ----------------------------------------------------------   1  recursive calls   0  db block gets  12  consistent gets   0  physical reads   0  redo size        3500  bytes sent via SQL*Net to client 578  bytes received via SQL*Net from client   7  SQL*Net roundtrips to/from client   2  sorts (memory)   0  sorts (disk)  90  rows processed

再看一个等值连接的

SQL> select ename,dname from emp a,dept b where a.deptno=b.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |        |    14 |   308 |     6 (17)| 00:00:01 | |   1 |  MERGE JOIN      |        |    14 |   308 |     6 (17)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2  (0)| 00:00:01 | |   3 |    INDEX FULL SCAN      | PK_DEPT |     4 |       |     1  (0)| 00:00:01 | |*  4 |   SORT JOIN      |        |    14 |   126 |     4 (25)| 00:00:01 | |   5 |    TABLE ACCESS FULL      | EMP     |    14 |   126 |     3  (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("A"."DEPTNO"="B"."DEPTNO")        filter("A"."DEPTNO"="B"."DEPTNO") Statistics ----------------------------------------------------------   1  recursive calls   0  db block gets  10  consistent gets   0  physical reads   0  redo size 819  bytes sent via SQL*Net to client 523  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   1  sorts (memory)   0  sorts (disk)  14  rows processed

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

文章名称：Oracle学习之性能优化（七）join的实现方式-创新互联
文章源于：http://kswjz.com/article/dehice.html