在查看执行计划中,大家熟知的oracle表的连接方式有几种主要表连接嵌套循环连接 排序合并连接 哈希连接三种,具体的如何使用这里就不用分析啦,google一下多得数不清楚,下面在这里通过表格来说明一下他们几个之间的区别
类别 |
嵌套循环连接 |
排序合并连接 |
哈希连接 |
优化器提示 |
USE_NL |
USE_MERGE |
USE_HASH |
使用的条件 |
任何连接 |
主要用于不等价连接,如<、 <=、 >、 >=; 但是不包括 <> |
仅用于等价连接 |
相关资源 |
CPU、磁盘I/O |
内存、临时空间 |
内存、临时空间 |
特点 |
当有高选择性索引或进行限制性搜索时效率比较高,能够快速返回第一次的搜索结果。 |
当缺乏索引或者索引条件模糊时,排序合并连接比嵌套循环有效。 |
当缺乏索引或者索引条件模糊时,哈希连接连接比嵌套循环有效。通常比排序合并连接快。 在数据仓库环境下,如果表的纪录数多,效率高。 |
缺点 |
当索引丢失或者查询条件限制不够时,效率很低; 当表的纪录数多时,效率低。 |
所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。 |
为建立哈希表,需要大量内存。第一次的结果返回较慢。 |
说明:此表是在iptub上面找到的,在这里分享一下
提示这里hash_join需要设置参数HASH_JOIN_ENABLED为True(注意此参数在10g之后已经obsolete),并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候(其实此处应该是hash_area_size),hash_join才能发挥最大功效。
那么,如何设置hash_area_size呢?在metalink上有这么一句话:
<Parameter:HASH_AREA_SIZE> :
specifies how much memory can be used to build a hash table for a HASH join , and resembles the SORT_AREA_SIZE parameter. If this parameter is set too small , then partial hash tables will need to be stored in temporary segments. If this parameter is set too big, then physical memory would be exhausted. As with SORT_AREA_SIZE, HASH_AREA_SIZE indicates how much memory can be used per session. Many concurrent sessions can consume a lot of memory.
The default value of HASH_AREA_SIZE = 2 * SORT_AREA_SIZE.
也就是说hash_area_size和sort_area_size一样,默认的情况下HASH_AREA_SIZE = 2 * SORT_AREA_SIZE.,并且不能设置太大,这样会消耗完物理内存,那么系统是不是这样的呢,我们来看一下:
SQL> show parameter area
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_retained_size integer 0
sort_area_size integer 65536
workarea_size_policy string AUTO
红色部分就是我们需要查看的参数。
通过经验得知,hash_area_size设置为驱动表的1.6倍左右,但是不建议超过2M,在Oracle9i及以后版本中,Oracle不推荐在dedi
1 NESTED LOOPS 循环嵌套连接
循环驱动表,用驱动表中的记录从外部表获得匹配,放到结果集中。
适用于
1.以小表做为驱动表或称为外部表
2.外部表选择性高的情况。
hint /*+ use_nl(表别名 表别名) */
2 群集连接(CLUSTER JOIN)
群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表,即两张表属于同一个段(SEGMENT),,那么ORACLE能够使 用群集连接。处理的过程是:ORACLE从第一张行源表中读取第一行,然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录;继续上面的步骤 处理行源表中的第二行,直到所有的记录全部处理完。
群集连接的效率极高,因为两个参加连接的行源表实际上处于同一个物理块上。但是,群集连接也有其限制,没有群集的两个表不可能用群集连接。所以,群集连接实际上很少使用。
3 排序合并连接(SORT MERGE JOIN)
将两个源表排序后合并
优缺点:
1.排序占用内存块大,磁盘IO频繁
2.适用于两个表源宠大,且选择性低的情况
3.where从句只能用等价连接
hint /*+ use_merge(表别名 表别名) */
4 哈稀连接
Oracle基于CBO(只能基于成本),选两个表中的小表,在内存中建立基于连接键的HASH表,优化器再选择另一张大表与哈稀表进行比较。
优缺点:
1.当小表能完全cache到内存时,效果最佳,成本只有将两个表从硬盘读入内存。
2.当哈稀表过大,内存不够用时,oracle会回写到磁盘造成IO消耗
3.where从句只能用等价连接
4.当内存足够大时,oracle通常选择此连接方式
hint /*+ use_hash(a b)*/
5 索引连接
如果一组已存在的索引包含了查询所需要的所有信息,那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引,而 选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列,而您只想访问有限的列时,这种方法非常有效。WHERE子 句约束条件越多,执行速度越快。因为优化器在评估执行查询的优化路径时,将把约束条件作为选项看待。您必须在合适的列(那些满足整个查询的列)上建立索 引,这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引,或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描,连接索 引的优势在于:快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。