Index on view (Oracle)

Question

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?

Answer 1

I'm not up-to-par with Oracle but I believe Materialized Views do exactly that.

Choosing Indexes for Materialized Views

The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements. Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitmap index is defined on each materialized view key column.

In the case of materialized views containing only joins using fast refresh, Oracle recommends that indexes be created on the columns that contain the rowids to improve the performance of the refresh operation.

If a materialized view using aggregates is fast refreshable, then an index is automatically created unless USING NO INDEX is specified in the CREATE MATERIALIZED VIEW statement.

Answer 2

You cannot create an index on a view, since a view is merely a mask on some tables(s). To do so, create a materialized view as specified by @Lieven and create an index on it.