Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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)
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)
Can somebody confirm or deny my observation?
Thanks
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 02 2005 - 04:19:08 CST
![]() |
![]() |