Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Optimization of Partitioned Outer Joins

RE: Optimization of Partitioned Outer Joins

From: Lex de Haan <>
Date: Wed, 5 Jan 2005 10:11:49 +0100
Message-Id: <>

Hi Jaromir,
one phenomenon that makes hash joins a little more tricky (it truly is a brilliant algorithm!) is the "dynamic role reversal" feature. at execution time, for any hash/probe set of partitions to be joined, Oracle will dynamically use the smallest one for the hash and the biggest one for the probe.
that's why there is no straightforward mapping between inner/outer vs hash/probe...



Tom Kyte Seminar:

-----Original Message-----
From: [] On Behalf Of jaromir nemec
Sent: Tuesday, January 04, 2005 23:54
Subject: Re: Optimization of Partitioned Outer Joins


great thanks for the clarification.

> [jl] Hash joins really ought to refer to the hash table and [jl] probe
> table. (and you can equate hash with outer, [jl] probe with inner -
> sort of).

May I summarise it as follows:

in case of nested loops and nested loops outer the 1st table in execution plan is the outer table, the 2nd table the inner.


| 0 | SELECT STATEMENT | | 1 | NESTED LOOPS OUTER| | 2 | TABLE ACCESS FULL| LT | <<< outer table

|* 3 | TABLE ACCESS FULL| ST | <<< inner table

Similar, in case of hash join, hash join outer and hash join right outer the 1st table in execution plan is the hashed table, the 2nd table the probed one.


| 0 | SELECT STATEMENT | | |* 1 | HASH JOIN RIGHT OUTER| | | 2 | TABLE ACCESS FULL | ST | <<< hashed table

| 3 | TABLE ACCESS FULL | LT | <<< probed table

As a analogy between NL and HASH (via same position) the hashed table is also referenced as outer table and the probed table is called inner table. ----Did I catch it right? If so, it sound perfectly logical to me.But I'm not sure if this definition is generally accepted - see the quote from 10g documentation below: .htm#76945

The outer table, including preserved rows, may be used to build the hash table, or it may be used to probe one.

 Here is the "outer table" used as a synonym for "preserved table", so the "outer table" can be both hash and probe table.I know, this is not a mortal problem of Oracle, but there 20 or so references of "outer table" in Oracle documentation (and a similar number of references of "inner table"). So time to time I'd like to have those things ordered.Particularly interesting in this context is the usage of USE_HASH (a) hint. The tablespec (a) in this hint should reference the "inner table" in the hash join - without the knowledge of the definition of "inner table" it could be difficult to hit the right table.OK, at least now I know, why I always use this hint in form USE_HASH(a b) Any comments and corrections welcome, Regards Jaromir D.B. Nemec


Received on Wed Jan 05 2005 - 03:12:55 CST

Original text of this message