Re: Performance problem in view joining UNION ALL three tables

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 08 Jul 2014 19:47:05 -0600
Message-ID: <53BC9F19.5040200_at_evdbt.com>

I had half of a chapter in our 1998 book "Oracle8 Data Warehousing" (John Wiley & Sons) devoted to partitioned UNION-ALL views, and as an Oracle employee at the time, the book was subject to review by Oracle. The reviewer excised every word on PVs, declaring that the (then-new) partitioning option in Oracle8 made PVs completely irrelevant. Gary and I fought like hell to keep something, but the Oracle reviewer would not budge a millimeter.

The script I shared earlier came originally from that book project, and I've used it on each subsequent version of the RDBMS to determine if the feature was finally removed.

On 7/8/14, 19:07, Jonathan Lewis wrote:
>
> 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-l

Received on Wed Jul 09 2014 - 03:47:05 CEST