Re: Optimization of Partitioned Outer Joins
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Jan 2005 23:54:14 +0100
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
Other related posts:
