1.1.2.2 表连接的方法
1 排序合并连接（sort merge join）
排序合并连接是一种两个表在做表连接时用排序操作（sort）和合并操作（merge）来得到连接结果集的表连接方法。
排序合并连接的优缺点及适用场景：
●排序合并连接的执行效率不如哈希连接，但是排序合并连接的使用范围更广，因为哈希连接只用于等值连接，排序合并连接除等值连接还用于其他连接条件（<、<=、>、>=）。
●排序合并连接不适合OLTP系统，对OLTP系统来说，排序是非常昂贵的操作。如果能避免排序操作，OLTP系统也可以使用排序合并连接。比如两个表虽然是排序合并连接，实际上并不需要排序，因为这两个表在各自的连接列都有索引。
●排序合并连接不存在驱动表的概念。
2 嵌套循环连接（nested loops join）
嵌套循环连接是一种两个表做表连接时依靠两层嵌套循环得到连接结果集的表连接方法。
嵌套循环连接的优缺点及适用场景：
●如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列存在唯一性索引（或者在被驱动表的连接列存在选择性很好的非唯一性索引），此时嵌套循环连接的执行效率非常高；如果驱动表所对应的驱动结果集的记录数很多，即使在被驱动表的连接列存在索引，此时使用嵌套循环连接的执行效率也不会高。
●大表也可以做驱动表，关键在于目标SQL的谓词条件能否将驱动结果集的数量降下来。
●嵌套循环连接可以快速响应。排序合并连接要等到做完排序后合并操作时才能开始返回数据，哈希连接要等到驱动结果集所对应的hash table全部建完后才能开始返回数据。
在oracle11g中，oracle引入向量I/O(vector I/O)。在引入向量I/O后，oracle可以将原先一批单块读需要耗费的物理I/O组合起来，然后用一个向量I/O批量处理它们。这样就实现了在单块读的数量不降低的情况下减少这些单块读所要耗费的物理I/O数量，从而提高嵌套循环连接的效率。
SQL> create index idx_t2 on t2(col2);
查看数据库版本
SQL> select * from v$version
2 ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create table t1(col1 number,col2 varchar2(1));
表已创建。
SQL> create table t2(col2 varchar2(1),col3 varchar2(2));
表已创建。
SQL> insert into t1 values(1,'A');
SQL> insert into t1 values(2,'B');
SQL> insert into t1 values(3,'C');
SQL> insert into t2 values('A','A2');
SQL> insert into t2 values('B','B2');
SQL> insert into t2 values('D','D2');
SQL> commit;
加入/*+ ordered use_nl(t2) */让执行计划走嵌套循环连接
SQL> set autotrace traceonly
SQL> select /*+ ordered use_nl(t2) */ t1.col1,t1.col2,t2.col3
2 from t1,t2
3 where t1.col2=t2.col2;
执行计划
----------------------------------------------------------
Plan hash value: 1054738919
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 3 | 60 | 6 (0)| 00:
00:01 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 3 | 60 | 6 (0)| 00:
00:01 |
| 3 | TABLE ACCESS FULL | T1 | 3 | 45 | 3 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN | IDX_T2 | 1 | | 0 (0)| 00:
00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 (0)| 00:
00:01 |
加入
1 select /*+ optimizer_feature_enable('9.2.0') ordered use_nl(t2) */ t1.col1,
t1.col2,t2.col3
2 from t1,t2
3* where t1.col2=t2.col2
SQL> /
执行计划
----------------------------------------------------------
Plan hash value: 2110740940
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 3 | 60 | 6 (17)| 00:
00:01 |
| 1 | MERGE JOIN | | 3 | 60 | 6 (17)| 00:
00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 3 | 15 | 2 (0)| 00:
00:01 |
| 3 | INDEX FULL SCAN | IDX_T2 | 3 | | 1 (0)| 00:
00:01 |
|* 4 | SORT JOIN | | 3 | 45 | 4 (25)| 00:
00:01 |
| 5 | TABLE ACCESS FULL | T1 | 3 | 45 | 3 (0)| 00:
00:01 |
3 哈希连接（hash join）
在oracle10g及以后的数据库版本中，优化器（实际上是CBO，哈希连接仅适用于CBO）在解析目标SQL时是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLE，而在oracle10g以前受限于隐含参数HASH_JOIN_ENABLE。
USE_HASH Hint的优先级比参数_HASH_JOIN_ENABLE高，即使_HASH_JOIN_ENABLE参数值为FALSE，CBO解析SQL时仍使用哈希连接。
哈希连接的优缺点及适用场景：
●哈希连接不一定会排序，大多数情况下不需要排序。
●哈希连接的驱动表的连接列的可选择性应尽可能好，可选择性会影响Hash Bucket的记录数，Hash Bucket的记录数会影响从该Hash Bucket中查找匹配记录的效率。
●哈希连接只适用于CBO和等值连接。
●哈希连接很适合于小表和大表之间做表连接而且连接结果集的记录较多的情况，特别是小表的连接列的可选择性非常好的情况，这时哈希连接的直线时间近似于全表扫描大表的耗费时间。
10104事件在诊断哈希连接的性能问题时非常有用
SQL> conn sys/oracle as sysdba;
SQL> alter session set sql_trace=true;
SQL> oradebug setmypid
SQL> oradebug event 10104 trace name context forever,level 1
SQL> set autotrace traceonly
SQL> select * from hr.departments a,hr.employees b where a.department_id=b.depa
tment_id;
SQL> oradebug tracefile_name
C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\trace\xe_ora_9052.trc
SQL> alter session set sql_trace=false;
SQL> oradebug event 10104 trace name context off;
4 笛卡尔连接（cross join，cartesian join）
笛卡尔连接又称笛卡尔乘积，是一种两个表在做表连接时没有任何连接条件的表连接方法。
笛卡尔连接实际上一种特殊的合并连接，和排序合并连接类似，只是笛卡尔连接不需要排序，而且在执行合并操作时没有连接条件。
SQL> select t1.col1,t1.col2,t2.col3 from t1,t2;
SQL> select t1.col1,t1.col2,t2.col3 from t1 cross join t2;
笛卡尔连接的优缺点及适用场景：
●笛卡尔连接的出现可能是由于漏写连接条件，可以利用笛卡尔连接减少对目标SQL中大表的全扫描次数。
●有时出现笛卡尔连接是因为目标SQL使用ORDERED Hint，同时在该SQL的位置相邻的两个表之间没有直接的关联条件。
●有时笛卡尔连接出现是因为统计信息不准确。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24986149/viewspace-1212628/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24986149/viewspace-1212628/
2126
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
