Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: Optimization of Partitioned Outer Joins

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 4 Jan 2005 23:54:14 +0100
Message-ID: <0ee001c4f2b0$52974df0$3c02a8c0@JARAWIN>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 04 2005 - 16:53:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US