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 planEach 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 tablesAnd 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. > > > > > > > > > > >
