在查看sql执行计划时,我们会发现表的连接方式有多种,本文对表的连接方式进行介绍以便更好看懂执行计划和理解sql执行原理。
一、连接方式:
嵌套循环(Nested Loops (NL))
(散列)哈希连接(Hash Join (HJ))
(归并)排序合并连接(Sort Merge Join (SMJ) )
二、连接说明:
1.Oracle一次只能连接两个表。不管查询中有多少个表,Oracle 在连接中一次仅能操作两张表。
2.当执行多个表的连接时,优化器从一个表开始,将它与另一个表连接;然后将中间结果与下一个表连接,以此类推,直到处理完所有表为止。
三、表连接详解:
1.嵌套循环(Nested Loops (NL)):
嵌套循环实现机制(伪代码):
For r1 in (select rows from table_1 where colx={value})
loop
for r2 in (select rows from table_2 that match current row from table_1)
loop
output values from current row of table_1 and current row of table_2;
end loop;
End loop;
这段代码由两个循环构成。
嵌套循环中的这两个表通常称为外部表(outer table)和内部表(inner table)。
在嵌套循环连接中,外部表又称为驱动表(driver table)
伪代码中:table_1为驱动表,table_2为内表
从伪代码中可以看出该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小结果集的表作为驱动表的原因。
NEST LOOP JOIN COST = 从第一个表取得数据的成本 + 从第一个表得到结果的基数 Х 对第二个表访问一次的成本
所以嵌套循环一般适合于驱动表记录集比较少(<10000)且内表有高效索引访问方式。
使用USE_NL(table_1 table_2)可强制CBO 执行嵌套循环连接。
驱动表确定:驱动表【select rows from table_1 where colx={value} 】一般为根据where条件能得到较小结果集的表,而不一定是整个表记录比较小的表。
2.(散列)哈希连接(Hash Join (HJ)):
Hash join一般用于一张小表和一张大表进行join时。在绝大多数情况下,hash join效率比其他join方式效率更高。
对于hash join的详细理解,可参看网上一篇文章写的比较透彻:http://www.hellodba.com/reader.php?ID=144&lang=cn
3.排序合并连接(Sort Merge Join (SMJ) ):
通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_1 table_2)来强制使用排序合并连接。
过程:将两个表排序,然后将排序后两个表合并。
四、连接方式总结:
1))嵌套循环(nest loop):
对于被连接的数据子集较小的情况,嵌套循环连接是较好的选择。在嵌套循环中,外表驱动内表,外表返回的每一行都要在内表中检索找到它匹配的行,因此整个查询返回的结果集不能太大(大于10000不合适),要把返回子集较小的表作为外表(驱动表),而且在内表的连接字段上一定要有索引。
2)哈希连接(hash join):
哈希连接是大数据集连接时常用的方式,优化器使用两个表中较小的表,利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。
这种方式适用于较小的表完全可以放入内存的情况,这样成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段。
哈希连接只能应用于等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。
3)排序合并连接(Sort Merge Join )
通常情况下哈希连接的效果都比排序合并连接要好。然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序归并连接的性能会忧于哈希连接。
五、连接方式应用场景:
1. 哈希连接只适用于等值连接。
2. 嵌套循环是行源连接方式,只适合小量数据连接。
哈希连接和排序合并连接是集合连接方式,适合大量数据连接。
3. 在等值连接方式下,返回少量记录(<10000)且内部表在连接列上存在索引,适合嵌套循环连接。若返回大量记录则适合哈希连接。
4. 在等值连接方式下,两个行源集合都很大,若连接列是高基数列,则适合哈希连接,否则适合排序合并连接。
5. 嵌套循环连接可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据。而其它两种连接方式则不行。
6. 排序合并连接的两个数据集可以并行处理,而嵌套循环和哈希连接不能.
注: 整理自网络
- 浏览: 129201 次
最新评论
-
yszzu:
java -cp "lib/*" 自动搜索 ...
JAVA CP命令 -
smallbug_vip:
能不能受累从百度上把图片缓存本地然后传给iteye。
vpn的实现原理 -
xiaoxuan_blog:
...
getResource()和getResourceAsStream以及路径问题
相关推荐
Oracle表连接方式,思路清晰,可以帮助初学者更好的掌握Oracle的表连接。
内连接 自然连接 左外连接 右外连接 笛卡尔连接 索引连接 嵌套连接
详解Oracle多种表连接方式,对内连接、外联结均有详细描述。
数据仓库技术是目前已知的比较成熟和被广泛采用的解决方案,用于整和电信运营企业内部所有分散...本文着重分析ORACLE SQL优化中对于系统性能影响极大的表连接方式、特点、适用范围,并对如何使用和优化做了详细的探讨。
ORACLE表连接方式的分析与优化_tony.doc
从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种不同连接方式:
Oracle的三种表连接方式 详细讲述• sort merge join(SMJ) • nest loop(NL) • hash join(HJ)
本文将主要从以下几个典型的例子来分析Oracle表的几种不同连接方式。
Oracle+表连接方式(内连接-外连接-自连接) 详细介绍的连接的类型及应用实例,一份值得看的数据库资料,强列建义下载
Oracle+表连接方式(内连接-外连接-自连接)+详解
oracle表的连接方式,oracle表的连接方式
这是因为ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询..数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性...
Oracle数据库3种主要表连接方式各自优劣对比
oracle多表连接技巧介绍了常见的多表连接应用实例
从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种连接方式
Oracle 表的扫描方式及连接方法,全表扫描,索引扫描,索引范围扫描,索引唯一扫描
介绍SQL语句中的多表连接查询。主要讲解了表连接的形式和笛卡尔积,程序也重点讲解了表的左连接、右连接、自然连接等基本概念。
本章主要讲解oracle客户端连接oracle效劳器,主要有以下内容: 1.配置本地net效劳名配置 2.用sqlplus连接; 3.用toad连接; 一、配置本地net效劳名 首先说明我们的效劳器的IP为60.220.246.53,oracle的SID为czdb。 ...
ORACLE 数据库配置 如何连接,连接方式
错误描述:oracle远程连接服务器出现 ORA-12170 TNS:连接超时 错误检查:有很多是oracle自身安装的问题,但是我这里服务器配置正常,监听正常,服务正常,远程可以ping通服务器。 这里主要是防火墙问题,解决办法: ...