Re: Partition range iteration over view

At first I thought you can use a materialized view in lieu of a view. It does work - if you equi-partitioned the MV with the 2 base tables. Unfortunately the MV can not be fast refreshed, since it contains UNION ALL. So it is unlikely the MV approach will work for you.

Interestingly, if you take pain to include the range of key values in your join view, the optimizer will be able to take advantage of PARTITION RANGE ITERATOR. Something like

create or replace view v as

select * from t1 where dt < '01-jan-2003' union all
select * from t1 where dt >='01-jan-2003' union all
select * from t2 where dt < '01-jan-2003' union all
select * from t2 where dt >='01-jan-2003';

You will end up with 4 ARTITION RANGE ITERATOR, one for each partition from each table. I guess what you provided is a simplified case, you might have more partitions than 2. So the view definition could get ugly. But looks like partition elimination will be possible in this case.

• Jusung Yang

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<brEpa.25724$i_3.1171969_at_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 Fri Apr 25 2003 - 20:31:40 CDT