Re: Performance problem in view joining UNION ALL three tables
- From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jul 2014 07:58:15 +0100
I also remember partitioned views being officially deprecated, but I don't
really see how that would work since the set operators are still valid, not
to mention that anything true of standalone views is generally also true of
inline views (I'm thinking of predicate pushing, key preservation etc), and
not everyone has partitioning anyway, so unless Oracle wanted to remove a
basic optimisation I'm not sure what they could really take away apart from
its status as a named feature.
William Robertson
On 9 Jul 2014, at 01:58, Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx> wrote:
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:
