Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Partition range iteration over view
There are a couple of hidden parameters
which may help, and the PUSH_PRED
hint may have some effect.
The parameters have names like:
_push_join_predicate
_push_join_union_view
There are also a couple of parameters
which affect Oracle's choice about partition
elimination when the partition key is
expressed only in the join clause.
If the driving (dimension) table is hashed, then partition elimination can only take place if Oracle runs a preliminary query against the dimension table to identify the actual values of date that are relevant. (Something like 'select distinct d_date from tab where mon='200301') Sometimes it helps to tell Oracle that it really is worth running this query, and this can be achieved by setting
_subquery_pruning_enabled = true
then tweaking
_subquery_pruning_cost_factor
_subquery_pruning_reduction
(I think low encourages, high discourages,
and the range is 1 to 100 but I can't remember -
check Metalink I saw a note about it there once,
and I'm quoting from memory).
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____UK_______April 22nd ____USA_(FL)_May 2nd ____Denmark__May 21-23rd ____Sweden___June ____Finland__September ____Norway___September Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK_(Manchester)_May x 2 ____Estonia___June (provisional) ____Australia_June (provisional) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Richard Kuhler" <noone_at_nowhere.com> wrote in message news:brEpa.25724$i_3.1171969_at_twister.socal.rr.com...Received on Fri Apr 25 2003 - 03:36:13 CDT
> 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
>