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