SQL 索引视图 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6531564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index on view (Oracle)
提问by Clinton
Lets say I have two tables, tab_aand tab_b.
假设我有两张桌子，tab_a并且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_aindexes col_xand tab_bindexes col_y, we should be able to do this with two index searches.
如果tab_a索引col_x和tab_b索引col_y，我们应该能够通过两个索引搜索来做到这一点。
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_aor tab_b) change.
但是，如果我可以在一个索引中的两个表上创建索引，甚至索引视图，以一种在源表（tab_a或tab_b）更改时立即自动更新的方式，那就太好了。
Is there a way to do this in Oracle?
有没有办法在 Oracle 中做到这一点？
回答by Lieven Keersmaekers
I'm not up-to-par with Oracle but I believe Materialized Viewsdo exactly that.
我不符合 Oracle 标准，但我相信物化视图正是这样做的。
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.
物化视图上最常见的两个操作是查询执行和快速刷新，每个操作都有不同的性能要求。查询执行可能需要访问物化视图键列的任何子集，并且可能需要连接和聚合这些列的子集。因此，如果在每个物化视图键列上定义了单列位图索引，则查询执行的性能通常最佳。
对于仅包含使用快速刷新的连接的物化视图，Oracle 建议在包含 rowid 的列上创建索引以提高刷新操作的性能。
如果使用聚合的物化视图可快速刷新，则除非在 CREATE MATERIALIZED VIEW 语句中指定 USING NO INDEX，否则将自动创建索引。
回答by Hasan Fahim
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.
您不能在视图上创建索引，因为视图只是某些表的掩码。为此，请创建@Lieven 指定的物化视图并在其上创建索引。
