Re: Partition range iteration over view

Upon taking a 2nd look, 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, looks promising...

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>

• 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 - 23:22:38 CDT