RE: Performance problem in view joining UNION ALL three tables
Date: Wed, 9 Jul 2014 01:07:22 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E012ED_at_exmbx05.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Iggy Fernandez [iggy_fernandez_at_hotmail.com] Sent: 09 July 2014 01:56
To: tim_at_evdbt.com; oracle-l_at_freelists.org 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_at_evdbt.com
> To: oracle-l_at_freelists.org
> 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.
> >
> >
> >
> >
> >
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 09 2014 - 03:07:22 CEST