Oracle 表连接

一 Oracle表连接分类

ORACLE从6的版本开始，优化器使用4种不同的表的连接方式：

1：嵌套循环连接（NESTED LOOP JOIN）

2：群集连接（CLUSTER JOIN）

3：排序合并连接（SORT MERGE JOIN）

4：笛卡尔连接（CARTESIAN JOIN）

ORACLE 7.3中新增加了：

5：哈希连接（HASH JOIN）

在ORACLE8中，新增加了：

6：索引连接（INDEX JOIN）

二 几种主要表连接的比较

1 类别：

1)嵌套循环连接；

2 )排序合并连接；

3 )哈希连接；

2 优化器提示：

1) USE_NL;

2 )USE_MERGE

3 )USE_HASH

3 使用条件：

1)任何连接；

2)主要用于不等价连接，如<,<=,>,>=,但是不包括<>;

3)仅用于等价连接

4 相关资源：

1) CPU、磁盘I/O;

2)内存，临时空间；

3)内存，临时空间；

5 特点：

1)当有高选择性索引或进行限制性搜索时效率比较高，能快速返回第一次的搜索结果；

2)当缺乏索引或者索引条件模糊时，排序合并连接比嵌套连接更有效；

3)当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。

通常比排序合并连接快；在数据仓库环境下，如果表的纪录数多，效率高；

6 缺点：

1)当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低；

2)所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据；

3)为建立哈希表，需要大量内存。第一次的结果返回较慢；

三 表连接详解

一、嵌套循环连接(nested loop)

1嵌套循环连接的内部处理的流程：

1）Oracle优化器根据基于规则RBO（rule based optimizer）或基于成本CBO（cost based optimizer）的原则，选择两个表中的一个作为驱动表，并指定其为外部表。

2）Oracle优化器再将另外一个表指定为内部表。

3）Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。

4）Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。

5）重复上述步骤，直到外部表中的所有纪录全部处理完。

6）最后产生满足要求的结果集。

2嵌套循环连接使用场景：

1)在驱动行源表（就是正在查找的记录）较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时，嵌套循环连接效果是比较理想的。

2)基于嵌套连接的特点，我们可以想得到，它在两个关联表的数据量相差比较大时采用，但整体上数据量都不应该太大。该关联方式适用于得到小数据量的查询操作。

3嵌套循环连接优势：

1)嵌套循环连接可以实现快速响应。

因为排序合并连接需要等到排序完后做合并操作时才能开始返回数据，而哈希连接则也等到驱动结果集所对应的HASH TABLE 全部构建完后才能开始返回数据。

它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。

这样，在理想情况下，终端用户就可以通过查询屏幕查看第一批记录，而在同时读取其他记录。

2)不管如何定义连接的条件或者模式，任何两行记录源可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。

4 嵌套循环连接缺陷：

1)然而，如果内部行源表（读取的第二张表）已连接的列上不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。

2)如果驱动表的记录非常庞大时，其他的连接方法可能更加有效。

5 嵌套循环连接有以下特性:

(1) 通常 sql 语句中驱动表只访问一次, 被驱动表访问多次

(2) 不必等待处理完成所有行前可以先返回部分已经处理完成的数据

(3) 在限制条件以及连接条件列上建立索引, 能够提高执行效率

(4) 支持所有类型的连接 (等值连接, 非等值连接, like 等)

6 nested loop 优化

1)首先，要确保结果集小的表为驱动表，结果集多的表为被驱动表。

这不意味着记录多的表不能作为驱动表, 只要通过谓词条件过滤后得到的结果集比较小，也可以作为驱动表。

2)其次，在驱动表的谓词条件列以及被驱动表的连接列上加上索引，能够显著的提高执行性能。

3)最后，如果要查询的列都在索引中，避免回表查询列信息时，又将进一步提高执行性能。

7 强制嵌套连接，指定驱动表

select/*+use_nl(dept,emp)*/empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

select/*+use_nl(emp,dept)*/empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

select/*+use_nl(emp,dept)*/empno,ename,dname from dept,emp where emp.deptno=dept.deptno;

SQL> select /*+use_nl(T1,T2)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

SQL> select /*+ leading(T1) use_nl(T2)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

SQL> select /*+ leading(T2) use_nl(T1)*/ empno,ename,dname from T1,T2 where T1.deptno=T2.deptno;

上面我使用的/*+ leading(t1) use_nl(t2)*/这个HINT的含义，USE_NL表示强制ORACLE的优化器使用嵌套循环的链接方式，leading(t1)表示T1作为驱动表。

8 autotrace

SQL> show user

USER 为 "SCOTT"

SQL> set autotrace on

SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色

SP2-0611: 启用 STATISTICS 报告时出错

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> grant plustrace to public;

授权成功。

SQL> conn scott/tiger

已连接。

SQL> set autotrace on

SQL> set linesize 200

SQL> select empno,ename,dname from dept,emp where dept.deptno=emp.deptno;

......

执行计划

----------------------------------------------------------

Plan hash value: 351108634

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 364 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 364 | 4 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

849 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL> create table t1 as select * from emp;

表已创建。

SQL> create table t2 as select * from dept;

表已创建。

t1,t2无索引和主、外键

SQL> select empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

.....

执行计划

----------------------------------------------------------

Plan hash value: 2959412835

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 770 | 7 (15)| 00:00:01 |

|* 1 | HASH JOIN | | 14 | 770 | 7 (15)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T2 | 4 | 88 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T1 | 14 | 462 | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."DEPTNO"="T2"."DEPTNO")

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

112 recursive calls

0 db block gets

25 consistent gets

2 physical reads

0 redo size

849 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

14 rows processed

二、群集连接（CLUSTER JOIN）

1群集连接内部处理的流程：

(1)ORACLE从第一张行源表中读取第一行；

(2)然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录；

(3)继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。

2 群集连接：

群集连接实际上是嵌套循环连接的一种特例。

3群集连接使用场景：

如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT），那么ORACLE能够使用群集连接。

4 群集连接优势：

群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。

5 群集连接缺陷：

群集连接也有其限制，没有群集的两个表不可能用群集连接。所以，群集连接实际上很少使用。

三、排序合并连接（SORT MERGE JOIN）

1 排序合并连接内部处理的流程：

1）优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则到第2步。

2）第一个源表排序

3）优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。

4）第二个源表排序

5）已经排过序的两个源表进行合并操作，并生成最终的结果集。

2排序合并连接(Merge Sort Join)特点：

1) 驱动表和被驱动表都是最多只被访问一次。

2) 排序合并连接的表无驱动顺序。

3) 排序合并连接的表需要排序，用到SORT_AREA_SIZE。

4) 排序合并连接不适用于的连接条件是：不等于<>，like,其中大于>，小于<，大于等于>=，小于等于<=，是可以适用于排序合并连接

5) 排序合并连接，如果有索引就可以排除排序。

3 排序合并连接(Merge Sort Join)优点：

在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（所选的数据超过表记录数的5%）时，排序合并连接将比嵌套循环连更加高效。

4 排序合并连接(Merge Sort Join)缺点：

排列合并连接需要比较大的临时内存块，以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

Merge Sort Join是一种古老经典的排序模型，类似于数据结构时代的合并排序算法。

Merge Sort Join引入的最大优势是避免同Nest Loop Join类似的大量随机读现象，但是同时也引入了Sort空间变化的问题。

随着海量数据处理场景的增多，Merge Sort Join暴露出缺陷的机会越来越多。

而Nest Loop Join的大量随机读问题，也是可以通过索引等常规手段加以优化。

5 hint

SQL> select /*+ ordered use_merge(t1,t2)*/ empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

SQL> select /*+ use_merge(t1,t2)*/ empno,ename,dname from t1,t2 where t1.deptno=t2.deptno;

.......

执行计划

----------------------------------------------------------

Plan hash value: 1792967693

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 770 | 8 (25)| 00:00:01 |

| 1 | MERGE JOIN | | 14 | 770 | 8 (25)| 00:00:01 |

| 2 | SORT JOIN | | 4 | 88 | 4 (25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T2 | 4 | 88 | 3 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 462 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL| T1 | 14 | 462 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("T1"."DEPTNO"="T2"."DEPTNO")

filter("T1"."DEPTNO"="T2"."DEPTNO")

Note

-----

- dynamic sampling used for this statement

统计信息

----------------------------------------------------------

7 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

773 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

14 rows processed

四、笛卡尔连接（CARTESIAN JOIN）

1 笛卡尔连接内部处理的流程：

笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。

2 笛卡尔连接特点：

如果第一个表的纪录数为m,第二个表的纪录数为n,则会产生m*n条纪录数。

由于笛卡尔连接会导致性能很差的SQL，因此一般也很少用到。

SQL> select empno,ename,dname from emp,dept;

......

已选择56行。

执行计划

----------------------------------------------------------

Plan hash value: 2034389985

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 56 | 1120 | 9 (0)| 00:00:01 |

| 1 | MERGE JOIN CARTESIAN| | 56 | 1120 | 9 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | DEPT | 4 | 40 | 3 (0)| 00:00:01 |

| 3 | BUFFER SORT | | 14 | 140 | 6 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------

统计信息

----------------------------------------------------------

288 recursive calls

0 db block gets

83 consistent gets

0 physical reads

0 redo size

1792 bytes sent via SQL*Net to client

418 bytes received via SQL*Net from client

5 SQL*Net roundtrips to/from client

11 sorts (memory)

0 sorts (disk)

56 rows processed

SQL> select power(14,5) from dual;

POWER(14,5)

-----------

537824

五、哈希连接(HASH JOIN)

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

1 哈希连接内部处理的流程：

1)哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；

2)优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

当哈希表构建完成后，进行下面的处理：

3）第二个大表进行扫描

4）如果大表不能完全cache到可用内存的时候，大表同样会分成很多分区

5）大表的第一个分区cache到内存

6）对大表第一个分区的数据进行扫描，并与哈希表进行比较，如果有匹配的纪录，添加到结果集里面

7）与第一个分区一样，其它的分区也类似处理。

8）所有的分区处理完后，ORACLE对产生的结果集进行归并，汇总，产生最终的结果。

2 哈希连接适用场景：

1)当内存能够提供足够的空间时，哈希（HASH）连接是Oracle优化器通常的选择。

2)当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。

3)哈希连接的驱动表所对应的连接列的选择性尽可能好。

4)哈希只能用于CBO，而且只能用于等值连接的条件。（即使是哈希反连接，ORACLE实际上也是将其换成等值连接）。

5)哈希连接很适用小表和大表之间做连接且连接结果集的记录数较多的情形，特别是小表的选择性非常好的情况下，这个时候哈希连接的执行时间就可以近似看做和全表扫描个个大表的费用时间相当。

6)当两个哈希连接的时候，如果在施加了目标SQL中指定的谓词条件后得到的数据量较小的那个结果集所对应的HASH TABLE能够完全被容纳在内存中（PGA的工作区），此时的哈希连接的执行效率非常高。

3 哈希连接(Hash Join)特点：

1)驱动表和被驱动表都是最多只被访问一次。

2)哈希连接的表有驱动顺序。

3)哈希表连接的表无需要排序，但是他在做连接之前做哈希运算的时候，会用到HASH_AREA_SIZE来创建哈希表。

4)哈希连接不适用于的连接条件是：不等于<>，大于>，小于<，小于等于<=，大于等于>=，like。

5)哈希连接索引列在表连接中无特殊要求，与单表情况无异。

6)只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候,Oracle才会使用哈希连接。

7)HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

4 哈希连接的成本:

只是两个表从硬盘读入到内存的成本。

5 哈希连接缺陷：

如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。ORACLE推荐的临时表空间的区间是1MB。临时表空间的区间大小由UNIFORMSIZE指定。

当哈希表过大或可用内存有限，哈希表不能完全CACHE到内存。随着满足连接条件的结果集的增加，可用内存会随之下降，这时已经CACHE到内存的数据可能会重新写回到硬盘去。如果出现这种情况，系统的性能就会下降。

6 哈希连接的由来：

对于排序合并连接，如果两个表在施加了目标SQL中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；

而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。

为了解决这个问题，于是ORACLE引进了哈希连接。

7 hint

SQL> select /*+ ordered use_hash(emp,dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ use_hash(emp,dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ leading(emp) use_hash(dept)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

SQL> select /*+ leading(dept) use_hash(emp)*/ empno,ename,dname from emp,dept where emp.deptno=dept.deptno;

/*

---nested loop---

SQL> select /*+ leading(dept) use_hash(emp)*/ empno,ename,dname from emp t1,dept t2 where t1.deptno=t2.deptno;

*/

六、索引连接(INDEX JOIN)

如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。

可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取决于WHERE子句中的可有条件。

在一张表有大量的列，而您只想访问有限的列时，这种方法非常有效。WHERE子句约束条件越多，执行速度越快。

因为优化器在评估执行查询的优化路径时，将把约束条件作为选项看待。

您必须在合适的列（那些满足整个查询的列）上建立索引，这样可以确保优化器将索引连接作为可选项之一。

这个任务通常牵涉到在没有索引，或者以前没有建立联合索引的列上增加索引。

索引连接优势：

相对于快速全局扫描，连接索引的优势在于：快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。

参考：http://lipengy.blog.51cto.com/2769396/1308702

ITPub博客

Oracle 表连接

最新文章