Partition range iteration over view

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 23 Apr 2003 22:27:19 GMT
Message-ID: <brEpa.25724$i_3.1171969@twister.socal.rr.com>

We have a view as a union of two identically partitioned tables. When doing a query against that view, Oracle wants to scan all partitions instead of applying the range from a join condition. The typical query looks something like this ...

select ...
from partitioned_view,

   date_dimension
where date_dimension.date = partitioned_view.partitioned_date   and date_dimension.month = ...

If we do this style of 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. Unfortunately, these queries come from an adhoc tool that isn't capable of embedding hints.

Anyone have any ideas how we can get Oracle to do a partition range scan here without hints? Any initialization parameters we can change? Modifications to the underlying tables or view?

Here's a sample demonstration if anyone wants to play with it ...

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' Thanks,
Richard Kuhler Received on Wed Apr 23 2003 - 17:27:19 CDT