FreeLists / oracle-l / Optimization of Partitioned Outer Joins

["Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>]

Hi

By testing this new 10g feature I noticed that even if a nested loop is =
used, the join condition is not used as access predicate on the inner =
table! To test it I used queries like the following one (based on the =
sample schema SH provided by Oracle...).

SELECT t.fiscal_month_number,=20
       nvl(sum(s.amount_sold),0) amount_sold
FROM sales s PARTITION BY (channel_id)
             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times t
               WHERE t.fiscal_year =3D 1998
               AND t.fiscal_quarter_number =3D 2
             ) t USING (time_id)
WHERE channel_id =3D 9
GROUP BY t.fiscal_month_number;

The only way I found to workaround this problem is to add another join =
to optimize the access to the inner table before doing the outer join, =
i.e. something like this:

SELECT t.fiscal_month_number,=20
       nvl(sum(s.amount_sold),0) amount_sold
FROM sales s PARTITION BY (channel_id)
             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times t
               WHERE t.fiscal_year =3D 1998
               AND t.fiscal_quarter_number =3D 2
             ) t USING (time_id)
             JOIN times USING (time_id)
WHERE channel_id =3D 9
AND t.fiscal_year =3D 1998
AND t.fiscal_quarter_number =3D 2
GROUP BY t.fiscal_month_number;


Can somebody confirm or deny my observation?

Thanks
Chris
--
http://www.freelists.org/webpage/oracle-l