RE: Performance problem in view joining UNION ALL three tables
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "jcdrpllist@xxxxxxxxx" <jcdrpllist@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jul 2014 00:19:50 +0000
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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Juan Carlos Reyes Pacheco [jcdrpllist@xxxxxxxxx]
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.
Other related posts:
