Unanswered: Problem: Partition pruning a fact table with partitioned time dimension.
I'm looking for a little help understanding the Oracle optimizer's partition pruning decisions for a fact/dim scenario that I'm testing. My situation is this:
1) The fact table is RANGE partitioned on the time surrogate key (actual_time_sk) which equates to calendar years. It currently has 10 partitions (2000 - 2009). It is also HASH subpartitoned (8 subpartitions) on another dimension's surrogate key.
2) The existing time dim is NOT partitioned and has an index on calendar_year.
3) I have created a new copy of the time dim which I have LIST partitioned on calendar_year (VARCHAR2).
The query is:
fct.actual_time_sk = tim.time_sk
AND tim.calendar_year = '2007'
When I use the existing time dim, the explain plan is:
In the first scenario, the time dimension is accessed by the calendar_year index and the fact table's range partition is determined by PARTITION RANGE SUBQUERY (i.e. partition pruning IS happening on the fact table).
In the second scenario, the time dimension is accesed by partition yet the fact table is accessed by PARTITION RANGE ALL (i.e. partition pruning is NOT happening on the fact table).
If anyone could shed some light on why the second scenario avoids partition pruning the fact table, it would be greatly appreciated.