RE: Optimization of Partitioned Outer Joins
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <jaromir@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 Jan 2005 10:11:49 +0100
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...
Cheers,
Lex.
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of jaromir nemec
Sent: Tuesday, January 04, 2005 23:54
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimization of Partitioned Outer Joins
Jonathan,
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.
example
| 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.
example
| 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:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/optimops
.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
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 03, 2005 11:01 PM
Subject: Re: Optimization of Partitioned Outer Joins
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
