Database Tuning (MOSC)

MOSC Banner

Understanding views with unions

edited Jan 24, 2011 6:03AM in Database Tuning (MOSC) 7 commentsAnswered
If I have a view, V that is created by doing something like:
create view V as 
select foo, bar from A where baz = 1
union all 
select foo, bar from B where baz = 0;

If I then query V and add additional criteria, why does Oracle not simply query each table using my criteria using appropriate indexes for each table and the combine the results together. In that case, I would expect my performance to be roughly equal to query_time(A) + query_time(B).

We have a situation where a query run against table A with criteria returns in 2 seconds and the query run against table B with criteria returns in 2 seconds, but the query run against the union of the two in a V does two FTS and takes approximately 20 minutes.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center