Re: Optimization of Partitioned Outer Joins
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 2 Jan 2005 15:51:20 -0000
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@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, January 02, 2005 10:24 AM
Subject: Optimization of Partitioned Outer Joins
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
