Oracle中的表和表之间的关联方式有哪几种
Tags： Oracle哈希连接嵌套循环排序合并连接笛卡尔积
目前为止，无论连接操作符如何，典型的连接类型共有3种：
① 排序合并连接（Sort Merge Join，简称SMJ），Oracle 6提供
② 嵌套循环（Nested Loops Join，简称NL），Oracle 6提供
③ 哈希连接（Hash Join，简称HJ），也叫散列连接，Oracle 7.3新增
另外，还有一种笛卡尔积（Merge Join Cartesian，简称MJC）连接，在Oracle 6版本的时候就已经提供，一般情况下，尽量避免使用。
对于Oracle 6提供的群集连接（Cluster Join）和Oracle 8提供的索引连接（Index Join），本书不做介绍。
在详细介绍这3类表连接方式之前，先创建表T_20161014_LHR_01共100行记录，T_20161014_LHR_02共100000行记录，创建脚本如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | DROP TABLE T_20161014_LHR_01 CASCADE CONSTRAINTS PURGE; DROP TABLE T_20161014_LHR_02 CASCADE CONSTRAINTS PURGE; CREATE TABLE T_20161014_LHR_01 ( ID NUMBER NOT NULL, N NUMBER, CONTENTS VARCHAR2(4000) ); CREATE TABLE T_20161014_LHR_02 ( ID NUMBER NOT NULL, T_20161014_LHR_01_ID NUMBER NOT NULL, N NUMBER, CONTENTS VARCHAR2(4000) ); EXECUTE DBMS_RANDOM.SEED(0); INSERT INTO T_20161014_LHR_01 SELECT ROWNUM, ROWNUM, DBMS_RANDOM.STRING('a', 50) FROM DUAL CONNECT BY LEVEL <= 100 ORDER BY DBMS_RANDOM.RANDOM; INSERT INTO T_20161014_LHR_02 SELECT ROWNUM, ROWNUM, ROWNUM, DBMS_RANDOM.STRING('b', 50) FROM DUAL CONNECT BY LEVEL <= 100000 ORDER BY DBMS_RANDOM.RANDOM; COMMIT; SELECT COUNT(*) FROM T_20161014_LHR_01; --100 SELECT COUNT(*) FROM T_20161014_LHR_02; --100000 |
（一）排序合并连接（SMJ）
如果连接属性上都建有索引，那么可利用索引已有的排序作合并连接。但如果在连接属性上没有索引时，那么需要首先对两表在连接属性上排序，对排序结果再作连接。
通常情况下，哈希连接的效果都比排序合并连接要好，然而如果行源已经被排过序，那么在执行排序合并连接时不需要再排序了，在这种情况下排序合并连接的性能会优于哈希连接。可以使用USE_MERGE(T1 T2)来强制使用排序合并连接。
如果相关联的表都是一个数量级，且其中一个或多个表在关联字段上有索引，那么此时使用该提示将可获得比其它两种JOIN方式更好的性能。需要注意的是，如果相关联的表是同一数量级，且相关联的表在关联字段上没有索引，那么该种方式下系统将会对所关联的表都进行全表扫描排序，其成本极高。所以，在有的数据库系统中，已不使用SMJ的关联方式，取而代之的是使用HJ的方式。
在Oracle数据库中有一个隐含参数“_OPTIMIZER_SORTMERGE_JOIN_ENABLED”控制着SMJ的启用和关闭，该参数默认值是TRUE，表示启用SMJ连接。
SMJ的连接方式示例如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | SYS@lhrdb> SET AUTOT TRACE EXP STAT SYS@lhrdb> SELECT /*+ ORDERED USE_MERGE(B) */ * 2 FROM T_20161014_LHR_01 A, T_20161014_LHR_02 B 3 WHERE A.ID = B.ID; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 703966114 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 397K| | 35572 (1)| 00:07:07 | | 1 | MERGE JOIN | | 100 | 397K| | 35572 (1)| 00:07:07 | | 2 | SORT JOIN | | 100 | 198K| | 3 (34)| 00:00:01 | | 3 | TABLE ACCESS FULL| T_20161014_LHR_01 | 100 | 198K| | 2 (0)| 00:00:01 | |* 4 | SORT JOIN | | 82709 | 160M| 430M| 35569 (1)| 00:07:07 | | 5 | TABLE ACCESS FULL| T_20161014_LHR_02 | 82709 | 160M| | 270 (2)| 00:00:04 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."ID"="B"."ID") filter("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 1050 consistent gets 0 physical reads 0 redo size 13950 bytes sent via SQL*Net to client 586 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 100 rows processed |
（二）嵌套循环（NL）
NL是一种比较高效的连接方式，内部表循环与外部表相匹配。这个连接方法有驱动表（外部表）的概念，该连接过程是一个2层嵌套循环。
在嵌套循环连接中，Oracle读取驱动表（外部表）中的每一行，然后在被驱动表（内部表）中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理驱动表中的下一行。这个过程一直继续，直到驱动表中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中。
嵌套循环连接可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以获取快速的响应。嵌套循环连接适用于大表和小表的关联，一般小表作为驱动表。
NL的连接方式示例如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | SYS@lhrdb> SET AUTOT TRACE EXP STAT SYS@lhrdb> SELECT /*+ LEADING(A) USE_NL(B) */ * 2 FROM T_20161014_LHR_01 A, T_20161014_LHR_02 B 3 WHERE A.ID = B.ID; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2807835513 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 397K| 26846 (2)| 00:05:23 | | 1 | NESTED LOOPS | | 100 | 397K| 26846 (2)| 00:05:23 | | 2 | TABLE ACCESS FULL| T_20161014_LHR_01 | 100 | 198K| 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T_20161014_LHR_02 | 1 | 2041 | 268 (2)| 00:00:04 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 98517 consistent gets 0 physical reads 0 redo size 13950 bytes sent via SQL*Net to client 586 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100 rows processed SYS@lhrdb> SELECT /*+ LEADING(B) USE_NL(A) */ * 2 FROM T_20161014_LHR_01 A, T_20161014_LHR_02 B 3 WHERE A.ID = B.ID; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2375126766 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 397K| 22939 (2)| 00:04:36 | | 1 | NESTED LOOPS | | 100 | 397K| 22939 (2)| 00:04:36 | | 2 | TABLE ACCESS FULL| T_20161014_LHR_02 | 82709 | 160M| 270 (2)| 00:00:04 | |* 3 | TABLE ACCESS FULL| T_20161014_LHR_01 | 1 | 2028 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 301061 consistent gets 0 physical reads 0 redo size 13950 bytes sent via SQL*Net to client 586 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 100 rows processed |
可以看出，用T_20161014_LHR_01（小表）做驱动表的时候，逻辑读为98517，而用T_20161014_LHR_02（大表）做驱动表的时候，逻辑读为301061，差异非常大，所以，在使用NL连接的时候，尽量选择结果集较小的表作为驱动表。
（三）哈希连接（HJ）
HJ的连接原理如下：首先把小表的哈希操作存放到内存中，然后用大表的每条记录做哈希，与之前小表的哈希值匹配。这种连接是在Oracle 7.3引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO（Cost Based Optimization，基于代价的优化器）优化器中。
哈希连接的连接过程如下所示：
（1）构建阶段：优化器首先选择一张小表作为驱动表，运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存（HASH_AREA_SIZE）中进行的，所以，运算很快。
（2）探测阶段：优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话，那么这些记录将会被丢弃而不进行探测。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O的性能。HJ也适用于两个大表的关联。
哈希连接也可以用USE_HASH(T1 T2)提示来强制使用。如果使用哈希连接，那么初始化参数HASH_AREA_SIZE必须足够的大，如果是Oracle 9i以上版本，那么Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY为AUTO，然后调整PGA_AGGREGATE_TARGET的大小即可。
在Oracle数据库中有一个隐含参数“_HASH_JOIN_ENABLED”控制着HJ的启用和关闭，该参数默认值是TRUE，表示启用HJ连接。
可以借助于10104事件所产生的trace文件来观察目标SQL在做哈希连接时的大致过程和一些统计信息（比如用了多少个Hash Partition、多少个Hash Bucket以及各个Hash Bucket都分别有多少条记录等），10104事件在实际诊断哈希连接的性能问题时非常有用。
1 2 | [root@rhel6lhr ~]# oerr ora 10104 10104, 00000, "dump hash join statistics to trace file" |
使用10104事件观察目标SQL做哈希连接的具体过程为：
1 2 3 4 5 | oradebug setmypid oradebug event 10104 trace name context forever, level 1 set autotrace traceonly 实际执行目标SQL（必须要实际执行该SQL，不能用explain plan for） oradebug tracefile_name |
HJ的连接方式示例如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | SYS@lhrdb> SET AUTOT TRACE EXP STAT SYS@lhrdb> SELECT /*+ leading(A) use_hash(B) */ * 2 FROM T_20161014_LHR_01 A, T_20161014_LHR_02 B 3 WHERE A.ID = B.ID; 100 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 35977193 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 397K| 273 (2)| 00:00:04 | |* 1 | HASH JOIN | | 100 | 397K| 273 (2)| 00:00:04 | | 2 | TABLE ACCESS FULL| T_20161014_LHR_01 | 100 | 198K| 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T_20161014_LHR_02 | 82709 | 160M| 270 (2)| 00:00:04 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 1057 consistent gets 0 physical reads 0 redo size 13950 bytes sent via SQL*Net to client 586 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 100 rows processed |
（四）笛卡尔积（MJC）
笛卡尔积不能算真正的连接方式。一般来讲，对于一个做笛卡尔积的SQL，要不是因为SQL的写法不正确（做MERGE JOIN的两张表没有关联条件），就是因为Oracle没有正确地收集表的统计信息从而导致生成了错误的执行计划，可以通过如下方式来解决：
① 对形成MJC的2个表进行表分析，收集正确的统计信息。
② 加/*+ OPT_PARAM('_OPTIMIZER_MJC_ENABLED' 'FALSE') */的Hint来限制SQL进行MJC的连接。
③ 对形成MJC的2个表加上有效的连接条件。
隐含参数“_OPTIMIZER_MJC_ENABLED”控制着MJC的启用和关闭，该参数默认值是TRUE，表示启用MJC连接。
MJC的连接方式示例如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | SYS@lhrdb> SET AUTOT TRACE EXP STAT SYS@lhrdb> SELECT * 2 FROM T_20161014_LHR_01 A, T_20161014_LHR_02 B 3 ; 10000000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 337631975 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 8270K| 31G| 22939 (2)| 00:04:36 | | 1 | MERGE JOIN CARTESIAN| | 8270K| 31G| 22939 (2)| 00:04:36 | | 2 | TABLE ACCESS FULL | T_20161014_LHR_02 | 82709 | 160M| 270 (2)| 00:00:04 | | 3 | BUFFER SORT | | 100 | 198K| 22669 (2)| 00:04:33 | | 4 | TABLE ACCESS FULL | T_20161014_LHR_01 | 100 | 198K| 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 100071 consistent gets 0 physical reads 0 redo size 748567590 bytes sent via SQL*Net to client 7333846 bytes received via SQL*Net from client 666668 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 10000000 rows processed |
可以看到，MJC的连接方式是非常耗费性能的，在生产库上尤其得避免这种连接方式。
总结
HJ、SMJ和NL连接随着数据量的增加，其连接速度可以简单地表示成下图所示样子：
对于DBA来说，掌握这3种表的连接方式可以对SQL优化起到至关重要的作用。对于这3种关联方式的详细对比，参考下表：
有关表的连接方式的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2127240/
