BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter