NESTED LOOPS & HASH JOINS & MERGE JOIN

参考Oracle官方文档：Performance Tuning Guide

1、NESTED LOOPS（嵌套循环链接）

Nested loop joins are useful when the following conditions are true:

■The database joins small subsets of data.

■The join condition is an efficient method of accessing the second table.

A nested loop join involves the following steps:

1.The optimizer determines the driving table and designates it as the outer table.

2.The other table is designated as the inner table.

3.For every row in the outer table, Oracle Database accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every

row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:

NESTED LOOPS

outer_loop

inner_loop

Nested Loop Join Hints

If the optimizer chooses to use some other join method, then you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.

hint:USE_NL链接

2、HASH JOINS（散列连接）

The database uses hash joins to join large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then

scans the larger table, probing the hash table to find the joined rows.

This method is best when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.

When the Optimizer Uses Hash Joins

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

■A large amount of data must be joined.

■A large fraction of a small table must be joined.

hint:USE_HASH

3、MERGE JOIN（排序合并连接）

Sort merge joins can join rows from two independent sources. Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better

than hash joins if both of the following conditions exist:

■The row sources are sorted already.

■A sort operation does not have to be done.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loop

joins for large data sets. You cannot use hash joins unless there is an equality condition.

In a merge join, there is no concept of a driving table. The join consists of two steps:

1.Sort join operation: Both the inputs are sorted on the join key.

2.Merge join operation: The sorted lists are merged together.

When the Optimizer Uses Sort Merge Joins

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

■The join condition between two tables is not an equijoin.

■Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

hint:USE_MERGE