oracle的两表连接⽅式,RACLE⼏种表连接⽅式的区别
在查看执⾏计划中，⼤家熟知的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上有这么⼀句话：
：
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    131072sort_area_retained_size              integer    0
sort_area_size                      integer    65536workarea_size_policy                string      AUTO
红⾊部分就是我们需要查看的参数。sql优化的几种方式
通过经验得知，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中分配的。
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⼦ 句约束条件越多，执⾏速度越快。因为优化器在评估执⾏查询的优化路径时，将把约束条件作为选项看待。您必须在合适的列(那些满⾜整个查询的列)上建⽴索 引，这样可以确保优化器将索引连接作为可选项之⼀。这个任务通常牵涉到在没有索引，或者以前没有建⽴联合索引的列上增加索引。相对于快速全局扫描，连接索 引的优势在于：快速全局扫描只有⼀个单⼀索引满⾜整个查询；索引连接可以有多个索引满⾜整个查询。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。