RE: Optimization of Partitioned Outer Joins
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 3 Jan 2005 00:15:34 +0100
Another info...
>The problem here is that I would like to see the filter 7 to be applied =
=3D
>in step 10 as access predicate. Of course since the index scan is "not =
=3D
>possible" the NL makes no sense here...
Without the partitioned outer join the same query runs as expected. =
=3D=3D> i.e. to me it seams that the partitioned outer join prevents the =
CBO to use the join predicate as access predicate!
SELECT t.fiscal_month_number,
nvl(sum(s.amount_sold),0) amount_sold
FROM sales s 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;
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 3 | NESTED LOOPS | |
|* 4 | TABLE ACCESS FULL | TIMES |
| 5 | PARTITION RANGE ITERATOR | |
| 6 | BITMAP CONVERSION TO ROWIDS | |
| 7 | BITMAP AND | |
|* 8 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX |
|* 9 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T"."FISCAL_YEAR"=3D1998 AND =
"T"."FISCAL_QUARTER_NUMBER"=3D2)
8 - access("TIME_ID"=3D"S"."TIME_ID")
9 - access("S"."CHANNEL_ID"=3D9)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
