Table Join Hints Oracle Tips by Burleson Consulting

Oracle provides a wealth of hints to direct the various types of table joins. As you will recall from an earlier chapter, Oracle can invoke a nested loop join, a sort merge join, a hash join, or a star join. Because table joins are the most time consuming of all Oracle SQL execution steps, the Oracle hints for table joins are frequently used to test the execution speed of various join techniques.

The use_hash Hint

The use_hash hint requests a hash join against the specified tables. Essentially, a hash join is a technique whereby Oracle loads the rows from the driving table (the smallest table, first after the where clause) into a RAM area defined by the hash_area_size initialization parameter (Figure 12-1).

Figure 1: A hash join and RAM usage

Oracle then uses a hashing technique to locate the rows in the larger second table. As I mention in Chapter 10, a hash join is often combined with parallel query in cases where both tables are very large.

The following query is an example of a query that has been hinted to force a hash join with parallel query:

select /*+ use_hash(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the execution plan for the hash join. Note that both tables in this join are using parallel query to obtain their rows:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     3
  HASH JOIN
                                                                     1
PARALLEL_TO_SERIAL
    TABLE ACCESS
FULL                           EMP                                   1
PARALLEL_TO_PARALLEL
    TABLE ACCESS
FULL                           BONUS                                 2

Hash joins are often faster than nested loop joins, especially in cases where the driving table is filtered into a small number of rows in the query’s where clause.

Enabling Your Database to Accept the use_hash Hint

The use_hash hint is very finicky, and there are many conditions that must be satisfied. It is not uncommon to find that a use_hash hint is ignored and here are some common causes of this problem.

• Check initialization parameters Make sure that you have the proper settings for optimizer_index_cost_adj, hash_multiblock_io_count, optimizer_max_permutations, and hash_area_size. You can see Chapter 16 for details on setting these parameters.

• Verify driving table Make sure that the smaller table is the driving table (the first table in the from clause). This is because a hash join builds the memory array using the driving table.

• Analyze CBO statistics Check that tables and/or columns of the join tables are appropriately analyzed.

• Check for skewed columns Histograms are recommended only for nonuniform column distributions. If necessary, you can override the join order chosen by the cost-based optimizer using the ordered hint.

• Check RAM region Ensure that hash_area_size is large enough to hold the smaller table in memory. Otherwise, Oracle must write to the TEMP tablespace, slowing down the hash join.

The use_merge Hint

The use_merge hint forces a sort merge operation. The sort merge operation is often used in conjunction with parallel query because a sort merge join always performs full-table scans against the tables. Sort merge joins are generally best for queries that produce very large result sets such as daily reports and table detail summary queries, or tables that do not possess indexes on the join keys. Here we see a simple query that has been formed to perform a sort merge using parallel query against both tables.

select /*+ use_merge(e,b) parallel(e, 4) parallel(b, 4) */
   e.ename,
   hiredate,
   b.comm
from
   emp e,
   bonus b
where
   e.ename = b.ename
;

Here is the output of the execution plan for this query. Note the full-table scans and the sort merge operation:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     5
  MERGE JOIN
                                                                     1
PARALLEL_TO_SERIAL
    SORT
JOIN                                                                 1
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           EMP                                   1
PARALLEL_TO_PARALLEL

    SORT
JOIN                                                                 2
PARALLEL_COMBINED_WITH_PARENT
      TABLE ACCESS
FULL                           BONUS                                 1
PARALLEL_TO_PARALLEL

It is important to note that a sort merge join does not use indexes to join the tables. In most cases, index access is faster, but a sort merge join may be appropriate for a large tale join without a where clause, or in queries that do nnotave available indexes to join the tables.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.