The advantage of Oracle INDEX-MERGE joins over fast full scans is that fast full scans have a single index satisfying the entire query. INDEX-MERGE joins have multiple indexes satisfying the entire query.

HASH joins in Oracle can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index.

A CLUSTER join is really just a special case of the NESTED LOOPS join that is not used very often. If the two row sources being joined are actually tables that are part of a cluster, and if the join is an equijoin between the cluster keys of the two tables, then Oracle can use a CLUSTER join.

In a SORT-MERGE join, Oracle sorts the first row source by its join columns, sorts the second row source by its join columns, and then merges the sorted row sources together. As matches are found, they are put into the result set.

NESTED LOOPS joins in Oracle are ideal when the driving row source  is small and the joined columns of the inner row source are uniquely indexed or have a highly selective nonunique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined.

Since the days of Oracle 6, the optimizer has used three primary ways to join row sources together: the nested loops join, the sort-merge join, and the cluster join. (There is also the favorite of the ad-hoc query user—the Cartesian join.) Oracle 7.3 introduced the hash join, and Oracle 8i introduced the index join, making for a total of five primary join methods. Each method has a unique set of features and limitations. Before you attack a potential join issue, you need to know the answers to the following questions:

The general SQL tuning principles remain the same in Oracle 11g, but some significant optimizer changes should be noted.

A helpful tip to remember is to use the EXISTS function instead of the IN function in most circumstances. The EXISTS function checks to find a single matching row to return the result in a subquery. Because the IN function retrieves and checks all rows, it is slower. Oracle has also improved the optimizer so it often performs this optimization for you as well. Consider the following example, where the IN function leads to very poor performance.

Function-based indexes allow you to create an index based on a function or expression. The value of the function or expression is specified by the person creating the index and is stored in the index. Function-based indexes can involve multiple columns, arithmetic expressions, or maybe a PL/SQL function or C callout.

Building the perfect system with all of the correctly indexed columns does not guarantee successful system performance. With the prevalence in business of bright-eyed ad-hoc query users comes a variety of tuning challenges. One of the most common is the suppression of perfectly good indexes.