ORACLE之 几种常用的表连接方式

几种常用的表连接方式

嵌套循环连接
在嵌套循环连接中，Oracle从第一个行源中读取第一行，然后和第二个行源中的数据进行对比。所匹配的记录放在结果集中，然后Oracle将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。第一个记录源通常称为外部表，或者驱动表，相应的第二个行源称为内部表。
使用嵌套循环连接是一种从连接结果中提取第一批记录的最快速的方法。
在驱动行源表（就是您正在查找的记录）较小、或者内部行源表已连接的列惟一的索引或高度可选的非惟一索引时， 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法优势，它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。这样，在理想情况下，终端用户就可以通过查询屏幕查看第一批记录，而在同时读取其他记录。不管如何定义连接的条件或者模式，任何两行记录源可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。
然而，如果内部行源表（读取的第二张表）已连接的列上不包含索引，或者索引不是高度可时，嵌套循环连接效率是很低的。如果驱动行源表（从驱动表中提取的记录）非常庞大时，其他的连接方法可能更加效。

图1-1说明了程序清单1-1中查询执行的方法。

select /*+ordered*/ename, dept.deptnofrom dept, empwhere dept.deptno=emp.deptno

排列合并连接
在排列合并连接中，Oracle分别将第一个源表、第二个源表按它们各自要连接的列排序，然后将两个已经排序的源表合并。如果找到匹配的数据，就放到结果集中。
在缺乏数据的择性或者可用的索引时，或者两个源表都过于庞大（超过记录数的5%）时，排序合并连接将比嵌套循环连更加高效。但是，排列合并连接只能用于等价连接（WHERE D.deptno=E.dejptno，而不是WHERE D.deptno>=E.deptno）。排列合并连接需要临时的内存块，以用于排序（如果SORT_AREA_SIZE设置得太小的话）。这将导致在临时表空间占用更多的内存和磁盘I/O。

图1-2解释了程序清单1-2查询执行的方法。

select /*+ordered*/ename, dept.deptnofrom emp, deptwhere dept.deptno=emp.deptno

哈希连接
当内存能够提供足够的空间时，哈希（HASH）连接是Oracle优化器通常的选择。
在哈希连接中，Oracle访问一张表（通常是较大的表），并在内存中建立一张基于连接键的哈希表。然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否匹配的记录。
只在数据库初始化参数 HASH_JOIN_ENABLED 设为True，并且为参数 PGA_AGGREGATE_TARGET 设置了一个足够大的值的时候，Oracle才会使用哈希表连接（HASH_AREA_SIZE是向下兼容的参数，但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE）。这和嵌套循环连接点类似--Oracle先建立一张哈希表以利于操作进行。当使用ORDERED提示时，FROM子句中的第一张表将用于建立哈希表。
当缺少用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接。

图1-3解释了执行程序清单1-3查询的方法。

select /*+ordered*/ename, dept.deptnofrom emp, deptwhere dept.deptno=emp.deptno

索引连接
在Oracle8i之前，您必须访问表，直到索引包含了所需要的信息。
从Oracle8i起，如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快速全局扫描访问到每一个索引，而选择何种扫描方式取决于 WHERE 子句中的可条件。在一张表有大量的列，而您只想访问有限的列时，这种方法非常有效。WHERE子句约束条件越多，执行速度越快。因为优化器在评估执行查询的优化路径时，将把约束条件作为项看待。
您必须在合适的列（那些满足整个查询的列）上建立索引，这样可以确保优化器将索引连接作为可项之一。这个任务通常牵涉到在没有索引，或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描，连接索引的优势在于：快速全局扫描只有一个单一索引满足整个查询。索引连接可以有多个索引满足整个查询。
程序清单1-4中的两个索引（一个在ENAME上，一个在DEPTNO上）创建于执行相应的查询之前。注意该查询不需要直接访问表！

图1-4解释了程序清单1-4  合并两个索引的查询

select ename, deptnofrom empwhere deptno=20and ename='DULLY';