Index on view (Oracle)

Viewed 62350

Lets say I have two tables, tab_a and tab_b.

And I create a view like the following:

create view join_tabs as
(
  select col_x as col_z from tab_a
  union
  select col_y as col_z from tab_b
);

And if I do the following:

select * from join_tabs where col_z = 'BLAH';

If tab_a indexes col_x and tab_b indexes col_y, we should be able to do this with two index searches.

However, it would be nice if I could make an index over both tables in one index, or even index the view, in a way that automatically updates immediately if the source tables (tab_a or tab_b) change.

Is there a way to do this in Oracle?

2 Answers
Related