Re: Partition range iteration over view

Hi,
it seems and if you view the explain-plan that oracle performs the view first and that's to late for iterating the partitions.

I have two ideas :
* Use MERGE NO_MERGE-Hint to influence the way oracle integrates the view into the whole query.
* Look for Query-rewrite-feature, Oracle should recognize that rwriting the query would result in better performance

Orca

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 Thu Apr 24 2003 - 02:12:32 CDT