Partition range (all) vs. Partition range (iterator)
---------------------------------------------------------
If query against the underlying partitioned tables,
we correctly get partition range iterations.  On the view however,
Oracle scans all partitions and doesn't join the date_dimension table
until the end. 


create table t1 (dt date)
    partition by range (dt) (
        partition old values less than
(to_date('01-jan-2003','dd-mon-yyyy')),
        partition new values less than (maxvalue)
    )
/

create table t2 (dt date)
    partition by range (dt) (
        partition old values less than
(to_date('01-jan-2003','dd-mon-yyyy')),
        partition new values less than (maxvalue)
    )
/

create table t3 (dt date, mon varchar2(7));

create view v as select * from t1 union all select * from t2;

select *
from t1, t3
where t1.dt = t3.dt
    and t3.mon = '2003-01'
/

Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
  NESTED LOOPS
    TABLE ACCESS (FULL) OF 'T3'
    PARTITION RANGE (ITERATOR)
      TABLE ACCESS (FULL) OF 'T1'


select *
from t3, v
where v.dt = t3.dt
    and t3.mon = '2003-01'
/

Execution Plan
----------------------------------------------------------
SELECT STATEMENT
  HASH JOIN
    VIEW OF 'V'
      UNION-ALL (PARTITION)
        PARTITION RANGE (ALL)
          TABLE ACCESS (FULL) OF 'T1'
        PARTITION RANGE (ALL)
          TABLE ACCESS (FULL) OF 'T2'
  TABLE ACCESS (FULL) OF 'T3'




** it seems that all you need is to use a dummy
where clause in your view that would give you all rows from the base
tables.

SQL> create or replace view v as
  2  select * from t1 where dt >='01-jan-1000' union all
  3  select * from t2 where dt >='01-jan-1000';

View created.

SQL> select * from t3, v
  2  where t3.dt=v.dt and t3.mon='2003-01';

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=9 Bytes=207)
   1    0   HASH JOIN (Cost=8 Card=9 Bytes=207)
   2    1     TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=1 Bytes=14)
   3    1     VIEW OF 'V' (Cost=5 Card=9 Bytes=81)
   4    3       UNION-ALL
   5    4         PARTITION RANGE (ITERATOR)
   6    5           TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=8 Bytes=7
          2)

   7    4         PARTITION RANGE (ITERATOR)
   8    7           TABLE ACCESS (FULL) OF 'T2' (Cost=3 Card=1 Bytes=7
          )

SQL>