Types of Materialized Views（实体化视图的类型）

In data warehouses, materialized views normally contain aggregates as shown in Example 8-1. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression. See "Restrictions on Fast Refresh on Materialized Views with Aggregates".

数据仓库中，通常，实体化视图包含聚集。对于快速刷新，select 列表必须包含group by子句中的所有列，并且，对于任何聚集列，必须有count(*)和count(column)列。实体化视图查询语句中所有的表必须有实体化视图日志。

Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND. A REFRESH ON COMMIT materialized view will be refreshed automatically when a transaction that does DML to one of the materialized view's detail tables commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

REFRESH ON COMMIT的实体化视图将自动刷新，当事务提交时。刷新操作作为提交过程的一部分，因此，完成提交所花时间会稍微长一些。该方法，对于多个用户并发改变实体化视图基表，不太合适。

Requirements for Using Materialized Views with Aggregates

（聚集实体化视图的要求）

Table 8-2 illustrates the aggregate requirements for materialized views.

Table 8-2 Requirements for Materialized Views with Aggregates

Note that COUNT(*) must always be present to guarantee all types of fast refresh. Otherwise, you may be limited to fast refresh after inserts only. Oracle recommends that you include the optional aggregates in column Z in the materialized view in order to obtain the most efficient and accurate fast refresh of the aggregates.

（通常，必须有COUNT(*)以保证所有类型的快速刷新，否则，只有插入操作的快速刷新）

Restrictions on Fast Refresh on Materialized Views with Aggregates

Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:

· All restrictions from "General Restrictions on Fast Refresh".

Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:（ON COMMIT and ON DEMAND实体化视图都支持快速刷新）聚集实体化视图，连接实体化视图

· All tables in the materialized view must have materialized view logs, and the materialized view logs must:

· （实体化视图中的所有表必须具有实体化视图日志，并且实体化视图日志还必须满足：1。包含实体化视图基表的所有列，并且基表中的列不允许加密。2。指定with rowid和including new values.。3。如果基表有serts/direct-loads, deletes, and updates操作，需指定sequence）

· Contain all columns from the table referenced in the materialized view. However, none of these columns in the base table can be encrypted.

· Specify with ROWID and INCLUDING NEW VALUES.

· Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

· Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh.

· COUNT(*) must be specified.

(必须指定COUNT(*))

· For each aggregate such as AVG(expr), the corresponding COUNT(expr) must be present. Oracle recommends that SUM(expr) be specified. See Table 8-2 for further details.

（对于每个聚集，必须存在对应的COUNT(expr)，oracle建议指定sum(expr)）

· If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. See Table 8-2 for further details.

(如果VARIANCE(expr) or STDDEV(expr)，必须指定COUNT(expr) and SUM(expr)，oracle建议指定SUM(expr *expr))

· The SELECT column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view.

· The SELECT list must contain all GROUP BY columns.

(SELECT list必须包含所有的GROUP BY columns)

· If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.（insert-only实体化视图，只有conventional DML inserts and direct loads支持快速刷新）

（1．有max和min聚集的实体化视图

2．有SUM(expr)聚集，但没有COUNT(expr)

3．没有COUNT(*)的实体化视图）

· Materialized views with MIN or MAX aggregates

· Materialized views which have SUM(expr) but no COUNT(expr)

· Materialized views without COUNT(*)

Such a materialized view is called an insert-only materialized view.

· A materialized view with MAX or MIN is fast refreshable after delete or mixed DML statements if it does not have a WHERE clause.

（有MAX or MIN的实体化视图，如果没有where子句，在delete or mixed DML statements后可快速刷新

· Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, refer to the Oracle Database Performance Tuning Guide.

· If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause.

（没有外部连接，where子句有任意选择和joins）

· Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by AND