Optimization of Partitioned Outer Joins
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 2 Jan 2005 11:24:55 +0100
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
Other related posts:
