RE: Performance problem in view joining UNION ALL three tables
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, "tim@xxxxxxxxx" <tim@xxxxxxxxx>
- Date: Wed, 9 Jul 2014 17:12:43 +0000
I pointed out in an earlier note that the PARTITION keyword doesn't necessarily
appear in newer versions of Oracle even though the plan follows the mechanisms
of partition views. Your output demonstrates exactly that phenomenon; partition
elimination is taking place under the UNION ALL - the eliminated partitions are
the ones under the FILTER operations.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jack Applewhite [jack.applewhite@xxxxxxxxxxxxx]
Sent: 09 July 2014 17:43
To: oracle-l@xxxxxxxxxxxxx; tim@xxxxxxxxx
Subject: RE: Performance problem in view joining UNION ALL three tables
Not in 11.2.0.4 Enterprise on ODA. Ran your very nice script but got Explain
Plans with no mention of Partition. I've attached my pvtest.lst.
Bummer. We need to get rid of some partitioned tables to avoid licensing.
----
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Tim Gorman <tim@xxxxxxxxx>
Sent: Tuesday, July 8, 2014 7:35 PM
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.
>
>
>
>
>
Confidentiality Notice: This email message, including all attachments, is for
the sole use of the intended recipient(s) and may contain confidential student
and/or employee information. Unauthorized use of disclosure is prohibited under
the federal Family Educational Rights & Privacy Act (20 U.S.C. §1232g, 34 CFR
Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code 21.355, 29 CFR
1630.14(b)(c)). If you are not the intended recipient, you may not use,
disclose, copy or disseminate this information. Please call the sender
immediately or reply by email and destroy all copies of the original message,
including attachments.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
