From oracle-l-bounce@freelists.org Mon Jan 3 10:05:24 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j03G5Nl19286 for ; Mon, 3 Jan 2005 10:05:23 -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 j03G5In19279 for ; Mon, 3 Jan 2005 10:05:19 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BA60C72C765; Mon, 3 Jan 2005 11:11:51 -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 30133-64; Mon, 3 Jan 2005 11:11:51 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4DE2672CB04; Mon, 3 Jan 2005 11:09:28 -0500 (EST) From: "Lex de Haan" To: , "'jaromir nemec'" Cc: Subject: RE: Optimization of Partitioned Outer Joins Date: Mon, 3 Jan 2005 17:05:44 +0100 MIME-Version: 1.0 Content-type: text/plain In-Reply-To: <2CF83791A616BB4DA203FFD13007824A01E6AFD2@MSXVS02.trivadis.com> X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcTxMBj/fa8PS/gwTNmMqtfP0r5WUwASu2rgAAtWDhA= Message-Id: <20050103165450.B392FDBBE@node42.naturaljoin.nl> Content-Transfer-Encoding: 8bit X-archive-position: 14318 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: lex.de.haan@naturaljoin.nl Precedence: normal Reply-To: lex.de.haan@naturaljoin.nl X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org The ANSI/ISO standard has the following to say about partitioned outer joins: (if you are not interested, hit the delete key now :-) ================ quote from 7.7: ========================= ::= PARTITION BY ::= [ { }... ] Syntax rule 7: If a PJT is specified, then: a) The or shall specify an . b) Each shall uniquely reference a column of the table referenced by the
simply contained in PJT. Such a column is called a join partitioning column. c) If the first operand of the or is a , then the shall be RIGHT or FULL. d) If the second operand of the or is a , then the shall be LEFT or FULL, and TRB shall not contain a containing an outer reference that references TRA. General Rule4: If RIGHT or FULL is specified or if LEFT is specified and the second operand specifies , then: a) Let TVB be the result of evaluating TRB. NOTE 138 - It follows from the Syntax Rules that TRB does not contain a containing an outer reference that references TRA. This ensures that it is possible to evaluate TRB in isolation. b) Case: i) If the first operand specifies , then Case: 1) If TVA is empty, then let NA be 0 (zero). 2) Otherwise, TVA is partitioned into the minimum numbers of partitions such that for each join partitioning column JPC of each partition, no two values of JPC are distinct. If the declared type of a join partitioning column is a user-defined type and the comparison of that column results in Unknown for two rows of TVA, then the assignment of those rows to partitions is implementation-dependent. Let NA be the number of partitions. Let GA1, ... , GANA be an enumeration of the partitions. ii) Otherwise, let NA be 1 (one), and let GA1 be TVA. c) Case: i) If the second operand specifies , then Case: 1) If TVB is empty, then let NB be 0 (zero). 2) Otherwise, TVB is partitioned into the minimum numbers of partitions such that for each join partitioning column JPC of each partition, no two values of the join partitioning column are distinct. If the declared type of a join partitioning column is a user-defined type and the comparison of that column results in Unknown for two rows of TVB, then the assignment of those rows to partitions is implementation-dependent. Let NB be the number of partitions. Let GB1, ... , GBNB be an enumeration of the partitions. ii) Otherwise, let NB be 1 (one), and let GB1 be TVB. d) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let PAi, j be the collection of rows RA of GAi for which there exists a row RB in GBj such that the concatenation of RA and RB is in TR. e) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let PBi, j be the collection of rows RB of GBi for which there exists a row RA in GAj such that the concatenation of RA and RB is in TR. f) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let UAi, j be the collection of rows of GAi that are not in PAi, j. g) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let UBi, j be the collection of rows of GBi that are not in PBi, j. h) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let XAi, j be UAi, j extended on the right with DB columns, with declared types and values determined as follows. For each k between 1 (one) and DB, the declared type of the (DA + k)-th column is the declared type of the k-th column of TVB, and the value is Case: i) If the k-th column of TVB is a join partitioning column, then the common value of the k-th column of GBj. ii) Otherwise, the null value. i) For each i between 1 (one) and NA, and for each j between 1 (one) and NB, let XBi, j be UBi, j extended on the left with DA columns, with declared types and values determined as follows. For each k between 1 (one) and DA, the declared type of the k-th column is the declared type of the k-th column of TVA, and the value is Case: i) If the k-th column of TVA is a join partitioning column, then the common value of the k-th column of GAi. ii) Otherwise, the null value. j) Let XA be the collection of all rows in XAi, j for all i between 1 (one) and NA and all j between 1 (one) and NB. NOTE 139 - If NA is 0 (zero), then XA is empty. k) Let XB be the collection of all rows in XBi, j, for all i between 1 (one) and NA and all j between 1 (one) and NB. NOTE 140 - If NB is 0 (zero), then XB is empty. l) Let XNB be an effective distinct name for XB. Conformance Rule 5: Without Feature F403, "Partitioned join tables", conforming SQL language shall not contain . Lex. ---------------------------------------------------------------- Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html ---------------------------------------------------------------- -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Christian Antognini Sent: Monday, January 03, 2005 11:17 To: jaromir nemec Cc: oracle-l@freelists.org Subject: RE: Optimization of Partitioned Outer Joins Hi Jaromir >> i.e. to me it seams that the partitioned outer join prevents the CBO >> = to >use the join predicate as access predicate! > >exact and in my opinion correct. Otherwise you can get a different = result of >the query as illustrated in a very simplified version of your query = below. Mhmm... I don't know what you are trying to show with your query... but = at least the ON clause is wrong... I guess that the following two = queries show what do you want to show. (notice that I have no problem = with the following results!) SQL> SELECT * 2 FROM sales s PARTITION BY (channel_id) 3 RIGHT OUTER JOIN ( 4 SELECT time_id 5 FROM times t 6 WHERE t.time_id =3D 1 7 ) t ON t.time_id =3D s.time_id 8 WHERE channel_id =3D 9 9 AND t.time_id =3D 1; CHANNEL_ID TIME_ID AMOUNT_SOLD TIME_ID ---------- ---------- ----------- ---------- 9 1 SQL> SELECT * 2 FROM sales s PARTITION BY (channel_id) 3 RIGHT OUTER JOIN ( 4 SELECT time_id 5 FROM times t 6 WHERE t.time_id =3D 1 7 ) t ON t.time_id =3D s.time_id 8 WHERE channel_id =3D 9 9 AND s.time_id =3D 1; no rows selected >My interpretation is, that the partitions are build first, followed by >= the >join. I agree, but I don't see why during the join the condition cannot be = used as access predicate. >Note, that in my example the required channel_id =3D 9 doesn't exist in >= the >constrained time (time_id =3D1) but is returned in the first query. The >= second >query, that constraint the sales table on time_id =3D 1 return no data. See my previous remark... >It will be interesting to see the ANSI definition of partitioned outer >= join. AFAIK it's not part to SQL 2003, therefore we have to wait some time. >Another minor point is the where clause (WHERE channel_id =3D9). In my >opinion, if the channel_id is known (and only one), there is no need to >perform partitioned join. This is a simplified query. In the original one the restriction was on = another column of the table CHANNELS, i.e. not on the PK. Chris -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l