Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Partition range iteration over view

Re: Partition range iteration over view

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 25 Apr 2003 09:36:13 +0100
Message-ID: <b8as6p$2il$1$8300dec7@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 - 03:36:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US