在查看执行计划中,大家熟知的oracle表的连接方式有几种主要表连接嵌套循环连接 排序合并连接 哈希连接三种,具体的如何使用这里就不用分析啦,google一下多得数不清楚,下面在这里通过表格来说明一下他们几个之间的区别
类别 | 嵌套循环连接 | 排序合并连接 | 哈希连接 |
优化器提示 | USE_NL | USE_MERGE | USE_HASH |
使用的条件 | 任何连接 | 主要用于不等价连接&#xff0c;如<、 <&#61;、 >、 >&#61;; 但是不包括 <> | 仅用于等价连接 |
相关资源 | CPU、磁盘I/O | 内存、临时空间 | 内存、临时空间 |
特点 | 当有高选择性索引或进行限制性搜索时效率比较高&#xff0c;能够快速返回第一次的搜索结果。 | 当缺乏索引或者索引条件模糊时&#xff0c;排序合并连接比嵌套循环有效。 | 当缺乏索引或者索引条件模糊时&#xff0c;哈希连接连接比嵌套循环有效。通常比排序合并连接快。 在数据仓库环境下&#xff0c;如果表的纪录数多&#xff0c;效率高。 |
缺点 | 当索引丢失或者查询条件限制不够时&#xff0c;效率很低&#xff1b; 当表的纪录数多时&#xff0c;效率低。 | 所有的表都需要排序。它为最优化的吞吐量而设计&#xff0c;并且在结果没有全部找到前不返回数据。 | 为建立哈希表&#xff0c;需要大量内存。第一次的结果返回较慢。 |
说明&#xff1a;此表是在iptub上面找到的&#xff0c;在这里分享一下
提示这里hash_join需要设置参数HASH_JOIN_ENABLED为True(注意此参数在10g之后已经obsolete)&#xff0c;并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候(其实此处应该是hash_area_size)&#xff0c;hash_join才能发挥最大功效。
那么&#xff0c;如何设置hash_area_size呢&#xff1f;在metalink上有这么一句话&#xff1a;
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 &#61; 2 * SORT_AREA_SIZE.
也就是说hash_area_size和sort_area_size一样&#xff0c;默认的情况下HASH_AREA_SIZE &#61; 2 * SORT_AREA_SIZE.&#xff0c;并且不能设置太大&#xff0c;这样会消耗完物理内存&#xff0c;那么系统是不是这样的呢&#xff0c;我们来看一下&#xff1a;
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
红色部分就是我们需要查看的参数。
通过经验得知&#xff0c;hash_area_size设置为驱动表的1.6倍左右&#xff0c;但是不建议超过2M&#xff0c;在Oracle9i及以后版本中&#xff0c;Oracle不推荐在dedicated server中使用这个参数来设置hash内存&#xff0c;而是推荐通过设置PGA_AGGRATE_TARGET参数来自动管理PGA内存。保留HASH_AREA_SIZE只是为了向后兼容。在dedicated server中&#xff0c;hash area是从PGA中分配的&#xff0c;而在MTS(Multi-Threaded Server)中&#xff0c;hash area是从UGA中分配的。
1 NESTED LOOPS 循环嵌套连接
循环驱动表&#xff0c;用驱动表中的记录从外部表获得匹配&#xff0c;放到结果集中。
适用于
1.以小表做为驱动表或称为外部表
2.外部表选择性高的情况。
hint /*&#43; use_nl(表别名 表别名) */
2 群集连接&#xff08;CLUSTER JOIN&#xff09;
群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表&#xff0c;即两张表属于同一个段&#xff08;SEGMENT&#xff09;,&#xff0c;那么ORACLE能够使 用群集连接。处理的过程是&#xff1a;ORACLE从第一张行源表中读取第一行&#xff0c;然后在第二张行源表中使用CLUSTER索引查找能够匹配到的纪录&#xff1b;继续上面的步骤 处理行源表中的第二行&#xff0c;直到所有的记录全部处理完。
群集连接的效率极高&#xff0c;因为两个参加连接的行源表实际上处于同一个物理块上。但是&#xff0c;群集连接也有其限制&#xff0c;没有群集的两个表不可能用群集连接。所以&#xff0c;群集连接实际上很少使用。
3 排序合并连接&#xff08;SORT MERGE JOIN&#xff09;
将两个源表排序后合并
优缺点&#xff1a;
1.排序占用内存块大&#xff0c;磁盘IO频繁
2.适用于两个表源宠大&#xff0c;且选择性低的情况
3.where从句只能用等价连接
hint /*&#43; use_merge(表别名 表别名) */
4 哈稀连接
Oracle基于CBO&#xff08;只能基于成本&#xff09;&#xff0c;选两个表中的小表&#xff0c;在内存中建立基于连接键的HASH表&#xff0c;优化器再选择另一张大表与哈稀表进行比较。
优缺点&#xff1a;
1.当小表能完全cache到内存时&#xff0c;效果最佳&#xff0c;成本只有将两个表从硬盘读入内存。
2.当哈稀表过大&#xff0c;内存不够用时&#xff0c;oracle会回写到磁盘造成IO消耗
3.where从句只能用等价连接
4.当内存足够大时&#xff0c;oracle通常选择此连接方式
hint /*&#43; use_hash(a b)*/
5 索引连接
如果一组已存在的索引包含了查询所需要的所有信息&#xff0c;那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引&#xff0c;而 选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列&#xff0c;而您只想访问有限的列时&#xff0c;这种方法非常有效。WHERE子 句约束条件越多&#xff0c;执行速度越快。因为优化器在评估执行查询的优化路径时&#xff0c;将把约束条件作为选项看待。您必须在合适的列&#xff08;那些满足整个查询的列&#xff09;上建立索 引&#xff0c;这样可以确保优化器将索引连接作为可选项之一。这个任务通常牵涉到在没有索引&#xff0c;或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描&#xff0c;连接索 引的优势在于&#xff1a;快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。