在查看执行计划中,大家熟知的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