Oracle Hint (1) ：UESE_HASH, USE_MERGE, USE_NL

1. USE_HASH (哈希连接)

The USE_HASH hint instructs the optimizer to join each specified table with another
row source using a hash join.

For example:
SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;

use_hash(table1,table2) 指定table1,table2连接以HASH 方式连接。

当内存能够提供足够的空间时,哈希(HASH)连接是Oracle优化器通常的选择。

在哈希连接中,Oracle访问一张表(通常是较小的表),并在内存中建立一张基于连接键的哈希表。

然后它扫描连接中其他的表（通常是较大的表），并根据哈希表检测是否有匹配的记录。

只有在数据库初始化参数HASH_JOIN_ENABLED设为True,并且为参数PGA_AGGREGATE_TARGET设置了一个足够大的值的时候, Oracle才会使用哈希边连接(HASH_AREA_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH_AREA_SIZE)。

这和嵌套循环连接有点类似——Oracle先建立一张哈希表以利于操作进行。当使用ORDERED提示时,FROM子句中的第一张表将用于建立哈希表。

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。

和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接。

Hash Join适合于小表与大表连接、返回大型结果集的连接。

2. USE_MERGE (排列合并连接)

The USE_MERGE hint instructs the optimizer to join each specified table with another row source using a sort-merge join.

For example:
SELECT /*+ USE_MERGE(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在排列合并连接中,Oracle分别将第一个源表、第二个源表按它们各自要连接的列排序,然后将两个已经排序的源表合并。如果找到匹配的数据,就放到结果集中。

在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。
但是,排列合并连接只能用于等价连接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno>=E.deptno)。
排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)，这将导致在临时表空间占用更多的内存和磁盘I/O。

对于USE_HASH和USE_MERGE，优化器会根据cost自动选择驱动表，如果我们非要控制table1为驱动表，可以加上leading(table1)作为提示，或者用ordered 作为提示。

3. USE_NL (嵌套循环连接)

The USE_NL hint instructs the optimizer to join each specified table to another row
source with a nested loops join, using the specified table as the inner table.

For example:

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity
FROM orders h, order_items l
WHERE l.order_id = h.order_id;

在嵌套循环连接中,Oracle从第一个行源中读取第一行,然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中,然后Oracle将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。第一个记录源通常称为外部表,或者驱动表,相应的第二个行源称为内部表。使用嵌套循环连接是一种从连接结果中提取第一批记录的最快速的方法。

在驱动行源表(就是您正在查找的记录)较小、或者内部行源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两行记录源可以使用嵌套循环连接,所以嵌套循环连接是非常灵活的。

然而,如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。

总结：

Hash join的主要资源消耗在于CPU（在内存中创建临时的hash表，并进行hash计算），而merge join的资源消耗主要在于此盘IO（扫描表或索引）。

在并行系统中，hash join对CPU的消耗更加明显。所以在CPU紧张时，最好限制使用hash join。

在绝大多数情况下，hash join效率比其他join方式效率更高。在Sort-Merge Join ，两张表的数据都需要先做排序，然后做merge。因此效率相对最差；Nested-Loop Join(NL)效率比SMJ更高。特别是当驱动表的数据量很大（集的势高）时。这样可以并行扫描内表。Hash join效率最高，因为只要对两张表扫描一次。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28933671/viewspace-762565/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28933671/viewspace-762565/