ORACLE几种表连接方式的区别

 在查看执行计划中，大家熟知的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不推荐在dedicated server中使用这个参数来设置hash内存，而是推荐通过设置PGA_AGGRATE_TARGET参数来自动管理PGA内存。保留HASH_AREA_SIZE只是为了向后兼容。在dedicated server中，hash area是从PGA中分配的，而在MTS(Multi-Threaded Server)中，hash area是从UGA中分配的。