179518.1 ...... metalink article ref.
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:b8as6p$2il$1$8300dec7_at_news.demon.co.uk...
>
> 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...
> > 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 - 04:15:21 CDT
