Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

WHERE clause on view with UNION ALL

rahulrasMay 15 2013 — edited May 15 2013

Hi All,

I am on Oracle v 11.2.0.1.

I have a view, which looks like

create view very_nice_view
as
select 'country1' as country, col1, col2
from  user1.table1  -- Table in user1 schema
union all
select 'country2' as country, col1, col2
from  user2.table2 -- Another table in user2 schema
union all
....

Now, somebody writes query like

select * from very_nice_view where country = 'country2'

How will Oracle treat this query? will Oracle collect data for full view and then filter out my data? or will Oracle only go to one table and give me my data?
If I want Oracle to look at only one table depending on the filter in WHERE clause of query, is it possible? How?

Thanks in advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 12 2013

Added on May 15 2013

#general-database-discussions

9 comments

8,020 views