Understanding views with unions
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.
0