视图索引(Oracle) [英] Index on view (Oracle)

问题描述

假设我有两个表，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
);

如果我执行以下操作:

select * from join_tabs where col_z = 'BLAH';

如果tab_a索引col_x和tab_b索引col_y，我们应该能够通过两次索引搜索来做到这一点.

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

但是，如果可以在源表(tab_a或tab_b)发生更改时立即自动更新的方式，可以在一个索引中的两个表上建立索引，甚至可以为视图建立索引，那就太好了.

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.

在Oracle中有没有办法做到这一点?

Is there a way to do this in Oracle?

推荐答案

我不了解Oracle，但我相信

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

关于 物化视图是查询执行 和快速刷新，以及每个操作 有不同的表现 要求.查询执行可能 需要访问的任何子集 物化视图键列，以及 可能需要加入并汇总 这些列的子集. 因此，查询执行通常 如果是单列，则效果最好 位图索引在每个上定义 物化视图键列.

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.

对于实例化视图 仅包含使用快速联接 刷新，Oracle建议 索引将在以下列上创建 包含rowid以改善 刷新操作的性能.

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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！