RE: Optimization of Partitioned Outer Joins

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Mon, 3 Jan 2005 11:16:47 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A01E6AFD2@MSXVS02.trivadis.com>

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

Received on Mon Jan 03 2005 - 04:11:13 CST