RE: Performance problem in view joining UNION ALL three tables
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jul 2014 01:07:22 +0000
Worth pointing out, though, that "partition views" as such were deprecated in
8i (possibly 8.0) and desupported in 9i - but only, I suspect, because the
optimizer was enhanced so that UNION ALL views could be optimised in all sorts
of ways that meant that the special restrictions of "partition views" were no
longer considered important.
You do get execution plans which include the PARTITION keyword with the VIEW
keyword - but many plans which show "partition view" behaviour will not include
the PARTITION keyword.
The target you tend to look for in the plans is the shape:
UNION ALL
FILTER
bit of plan
FILTER
bit of plan
...
FILTER
bit of plan
Each filter is derived from the predicate or constraint you added to each
table, and is used to eliminate a table (the filter predicates will be
tautologies - possibly "NULL IS NOT NULL" in modern versions. And some of the
FILTERs will be absent - showing you which branches of the UNION ALL were
actually called.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Iggy Fernandez [iggy_fernandez@xxxxxxxxxxx]
Sent: 09 July 2014 01:56
To: tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Performance problem in view joining UNION ALL three tables
And in 12c, UNION ALL can also be executed in parallel. Partitioned views are
the best kept secret of Oracle Database. They will never go away (regardless of
the fact that the last time they were found in the documentation is Oracle 7)
because they can do things that "heavy-duty" partitioning cannot. For example,
the partitions can be on remote databases. The partitions can have different
structures (heap, IOT, cluster) and can have different indexes.
Iggy
> Date: Tue, 8 Jul 2014 18:35:43 -0600
> From: tim@xxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Performance problem in view joining UNION ALL three tables
>
> Partitioned UNION-ALL views still worked as of 11.2; attached is a
> sample test-case script to demonstrate.
>
>
>
> On 7/8/14, 18:11, Juan Carlos Reyes Pacheco wrote:
> > Hello, If you please have some advice.
> > I have standard edition 11.2.0.3
> >
> > and I have view, like a primitive partitioning
> >
> > This is an old topic
> > https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21547067945222
> > buty maybe there is a new trick on 11g.
> >
> > the view is like
> > view a
> > is
> > select date,* from table_01ene1990_01ene2000
> > union all
> > select date,* from table_02ene2000_01ene2010
> > union all
> > select date,* from table_02ene2010_today
> >
> > I will like to know if there is some trick to get only access to the
> > table,
> > where the date is,
> >
> >
> >
> > In some situations I did it using a function table, the function table
> > only query the specific table.
> > But in other, like joinings I can't do that.
> >
> > The only I remember from oracle 7 an option I saw was outdated.
> >
> > Thank you.
> >
> >
> >
> >
> >
>
Other related posts:
