Re: Optimization of Partitioned Outer Joins

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 2 Jan 2005 15:51:20 -0000
Message-ID: <018f01c4f0e2$e76ecd10$6702a8c0@Primary>

To avoid confusion, could you post the execution paths of both queries ? Which table are you calling the inner table - from your choice of table to 'add', it looks like you are considering the TIMES table to be the inner, but the original times table is the preserved table in the outer join, so for a nested loop it would be the outer table.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

Original Message ----- From: "Christian Antognini" <Christian.Antognini_at_trivadis.com> To: <oracle-l_at_freelists.org> Sent: Sunday, January 02, 2005 10:24 AM Subject: Optimization of Partitioned Outer Joins

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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jan 02 2005 - 09:49:55 CST