RE: Optimization of Partitioned Outer Joins
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- Date: Mon, 3 Jan 2005 11:16:47 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
