Re: Optimization of Partitioned Outer Joins

Note in-line
(preceded by [jl])

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

• Original Message ----- From: "jaromir nemec" <jaromir_at_db-nemec.com> To: <oracle-l_at_freelists.org> Sent: Monday, January 03, 2005 9:37 PM Subject: Re: Optimization of Partitioned Outer Joins

Hi,

> but the original times table is
> the preserved table in the outer join, so for a
> nested loop it would be the outer table.

The preserved table is equal the outer table for SM outer join and for hash outer join as well, isn't it?

[jl] See note below about inner/outer - but ignoring that,
[j] 10g is more flexible with outer joins than earlier version.
[jl] in particular (and the powerpoint pdf from my OW
[jl] presentation has a slide on this one) the preserved
[jl] table can be the second of the two tables in the join
[jl] order for a hash join. pre-10g, the preserved table
[jl] HAD to be the hash table, from 10g the preserved
[jl] table can be the probe table.

I'm little bit confused from the formulation above, based on the nested loop context.

[jl] The terms inner and outer really ought to be reserved for
[jl] nested loop join because they derive from the concept of
[jl] coding through an 'outer for loop' and 'inner for loop'
[jl] The order of two tables is not significant for merge joins -
[jl] both data sets have to be presented in sorted order, so
[jl] the symmetry makes a nonsense of saying inner/outer,
[jl] first and second would be sufficient - although technically
[jl] there are some cases where the mechanics can make
[jl] a difference to the performance.

By the way, what is the correct definition of an outer table in an (inner) hash join? (There is no preserved table to be used in the definition; in case that ORDERED hint isn't used, it is not obvious what table is hashed and what table is probed).

[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).

The definition of "outer table" is particularly important e.g. for the hint PQ_DISTRIBUTE.
Can anybody clarify it for me?

thanks

Jaromir

• Original Message ----- From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> To: <oracle-l_at_freelists.org> Sent: Sunday, January 02, 2005 4:51 PM Subject: Re: Optimization of Partitioned Outer Joins

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l