Optimization of Partitioned Outer Joins

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Sun, 2 Jan 2005 11:24:55 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A01E6AFB5@MSXVS02.trivadis.com>

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

Received on Sun Jan 02 2005 - 04:19:08 CST