From oracle-l-bounce@freelists.org Mon Jan 3 16:00:44 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j03M0i804350 for ; Mon, 3 Jan 2005 16:00:44 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j03M0hn04345 for ; Mon, 3 Jan 2005 16:00:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CEC1672C15B; Mon, 3 Jan 2005 17:07:16 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13724-74; Mon, 3 Jan 2005 17:07:16 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 42C8372C7CE; Mon, 3 Jan 2005 17:04:47 -0500 (EST) Message-ID: <023501c4f1df$ba2b7df0$6702a8c0@Primary> From: "Jonathan Lewis" To: References: <2CF83791A616BB4DA203FFD13007824A01E6AFB5@MSXVS02.trivadis.com> <018f01c4f0e2$e76ecd10$6702a8c0@Primary> <0e8101c4f1dc$7b7b6500$3c02a8c0@JARAWIN> Subject: Re: Optimization of Partitioned Outer Joins Date: Mon, 3 Jan 2005 22:01:07 -0000 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-archive-position: 14327 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: jonathan@jlcomp.demon.co.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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" To: 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" To: 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