Re: Performance problem in view joining UNION ALL three tables

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Wed, 9 Jul 2014 18:52:28 -0400
Message-ID: <CAGYrQyvOJqZO9_cQs=NCaCMYx9veXyWJ33uMN0bzEOQGFpkfCg_at_mail.gmail.com>

Thank you Jonathan
The date is in example the date of the transaction.and divided in table for historic purposes

2014-07-08 20:19 GMT-04:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:

>
> Is "date" a column in the table, or did you mean sysdate, or a literal
> date ?
>
> If you have a query that need only access one of the three tables then
> you need to start by making it very clear to Oracle that the three tables
> have disjoint data sets, either by adding a constraint to each table that
> effectively defines its content, or by adding a where clause to each branch
> of the UNION ALL that effectively defines what's in the table. This gives
> the optimizer an option for considering "partition elimination".
>
> Beyond that you'll have to give a more realistic example of something
> you want to work.
>
>
>
> 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 Juan Carlos Reyes Pacheco [jcdrpllist_at_gmail.com]
> *Sent:* 09 July 2014 01:11
> *To:* ORACLE-L
> *Subject:* Performance problem in view joining UNION ALL three tables
>
> 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 Thu Jul 10 2014 - 00:52:28 CEST