How to switch the driving table in a hash join [ID 171940.1]

How to switch the driving table in a hash join [ID 171940.1]

--------------------------------------------------------------------------------
 
  修改时间 13-JUL-2006     类型 BULLETIN     状态 PUBLISHED

*************************************************************
This article is being delivered in Draft form and may contain
errors.  Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
PURPOSE
-------

Sometimes it may be difficult to get a particular table to drive a query.
This article shows a specific example of switching the driving table in a
hash join

 
SCOPE & APPLICATION
-------------------

This article provides background information for analysts wishing to tune
queries.

Using the SWAP_JOIN_INPUTS hint to switch the driving table in a hash join
--------------------------------------------------------------------------

Consider the following SQL statement:

SELECT *
FROM   emp, dept, dual
WHERE  emp.deptno = dept.deptno AND dept.dname = dual.dummy;

(Obviously this query makes no sense, it is just used for illustration).

A typical plan this generates is:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
   2    1     HASH JOIN (Cost=5 Card=14 Bytes=700)
   3    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   5    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)

In other words the query joins EMP and DEPT using a hash join and then joins to
DUAL using another hash join. If, for performance reasons, a different plan is
desired that drives from the DUAL table, such as:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)
   3    1     HASH JOIN (Cost=5 Card=14 Bytes=700)
   4    3       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   5    3       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)

Then there may be issues generating it.

The problem with achieving this plan is that the Hash Join between EMP and
DEPT cannot be directly referenced in a hint - it has no alias.

The way to achieve the above plan is to use the SWAP_JOIN_INPUTS hint as
shown below:

 
SQL> set autotrace trace explain
SQL>
SQL> SELECT /*+ ORDERED USE_HASH(dept) SWAP_JOIN_INPUTS(dual) */ *
  2  FROM   emp, dept, dual
  3  WHERE  emp.deptno = dept.deptno AND dept.dname = dual.dummy
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)
   3    1     HASH JOIN (Cost=5 Card=14 Bytes=700)
   4    3       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   5    3       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)

The hint says:

o ORDERED: Use the join order emp - dept - dual, use a
o USE_HASH(dept): Use a hash join with DEPT as the inner table
o SWAP_JOIN_INPUTS(dual): Swap the join inputs involving DUAL
Reference note Note:29236

And thus resutls in the desired plan.

Additional Information
======================

The following are a number of other hints that could be attempted to achieve
the same result (and failed):

Ordered hint:
=============

Here the desired DUAL, (EMP DEPT) join order is specified and hash joins
suggested. However because there is no join between dual and emp in the query,
this results in a cartesian product.

SQL> SELECT /*+ ordered use_hash(dept) */ *
  2  FROM dual,emp,dept
  3  WHERE emp.deptno = dept.deptno AND dept.dname = dual.dummy
  4  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16352 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=16352 Card=28588 Bytes=1486576)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
   3    1     MERGE JOIN (CARTESIAN) (Cost=16347 Card=114352 Bytes=3887968)
   4    3       TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)
   5    3       BUFFER (SORT) (Cost=16336 Card=14 Bytes=448)
   6    5         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)

Leading hint:
=============

Thie hint contains a Leading hint on DUAL to force that table to be accessed
first in the join order, then hash hints on the tables to. DUAL is accessed
first but this is then joined to DEPT and the result set joined to EMP, so the
access path is not the same.

SQL> SELECT /*+ leading(dual) use_hash(dept) use_hash(emp) use_hash(dual) */ *
  2  FROM   dual, dept, emp
  3  WHERE  emp.deptno = dept.deptno AND dept.dname = dual.dummy
  4  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=21 Card=28588 Bytes=1486576)
   2    1     HASH JOIN (Cost=15 Card=8168 Bytes=163360)
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)
   4    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
   5    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)

The author acknowledges that there may well be some other method of achieving
this join order and method combination. The SWAP_JOIN_INPUTS hint is one
way of achieving this.

NOTE
====
The SWAP_JOIN_INPUTS hint can be overridden by setting <Event:10103>.
For example:

SQL> alter session set events '10103 trace name context forever, level 1';

Session altered.

SQL> select /*+ ordered use_hash(dept) swap_join_inputs(dual) use_hash(dual) */ *
  2  from emp,dept,dual
  3  where emp.deptno = dept.deptno and dept.dname = dual.dummy
  4  ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=28588 Bytes=1486576)
   1    0   HASH JOIN (Cost=17 Card=28588 Bytes=1486576)
   2    1     HASH JOIN (Cost=5 Card=14 Bytes=700)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=448)
   4    2       TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=72)
   5    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=8168 Bytes=16336)

How to process the examples
-------------------------------------------------------
- Create the sample database with the scott user Note:100078.1
- run analyze on the 'scott' schema or the objects involved Note:1011835.102).
- set the hash_joined_enabled = true in init<sid>.ora

 

 

 

from:metalink [ID 171940.1]