物化视图限制

必须记住以下限制:

• 定义物化视图时的查询语句不能包含任何不可重复性的表达式(ROWNUM, SYSDATE, 不可重复的PL/SQL功能 等).
  

• 查询不能包含任何引用到RAW, LONG RAW或对象REFs.

• 如果这个物化视图被标识为PREBUILD，除非使用了WITH REDUCED PRECISION子句，列的精度必须和相对应的SELECT表达式的精度一致。

一般的查询重写限制

必须记住以下限制:

• 一个查询可以引用本地和远程的表，只要物化视图可以在本地引用相同的表就可能实现这类的查询重写。

• 明细表和物化视图都不能属于SYS用户。

• 如果一个列或表达式位于GROUP BY 子句，那么它也必须在SELECT列表里。

• Aggregate 功能必须位于表达式的最外部。如 AVG(AVG(x)) or AVG(x)+ AVG(x) 就不行.

• CONNECT BY子句不能用.

General Restrictions on Fast Refresh

The defining query of the materialized view is restricted as follows:

• The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

• The materialized view must not contain references to RAW or LONG RAW data types.

• It cannot contain a SELECT list subquery.

• It cannot contain analytical functions (for example, RANK) in the SELECT clause.

• It cannot contain a MODEL clause.

• It cannot contain a HAVING clause with a subquery.

• It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

• It cannot contain a [START WITH …] CONNECT BY clause.

• It cannot contain multiple detail tables at different sites.

• ON COMMIT materialized views cannot have remote detail tables.

• Nested materialized views must have a join or aggregate.

Restrictions on Fast Refresh on Materialized Views with Joins Only

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

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

• They cannot have GROUP BY clauses or aggregates.

• Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

• Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

• You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.

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:

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

  • 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.

• Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.

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

• 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 for further details.

• 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.

• If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.

  • 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.

• 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.

• 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 ANDs and must use the equality (=) operator.

• For materialized views with CUBE, ROLLUP, grouping sets, or concatenation of them, the following restrictions apply:

  • The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable.

  • GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

Restrictions on Fast Refresh on Materialized Views with UNION ALL

Materialized views with the UNION ALL set operator support the REFRESH FAST option if the following conditions are satisfied:

• The defining query must have the UNION ALL operator at the top level.

  The UNION ALL operator cannot be embedded inside a subquery, with one exception: The UNION ALL can be in a subquery in the FROM clause provided the defining query is of the form. SELECT * FROM (view or subquery with UNION ALL) as in the following example:

  CREATE VIEW view_with_unionall AS
  (SELECT c.rowid crid, c.cust_id, 2 umarker
   FROM customers c WHERE c.cust_last_name = 'Smith'
   UNION ALL
   SELECT c.rowid crid, c.cust_id, 3 umarker
   FROM customers c WHERE c.cust_last_name = 'Jones');
  
  CREATE MATERIALIZED VIEW unionall_inside_view_mv
  REFRESH FAST ON DEMAND AS
  SELECT * FROM view_with_unionall;
  

  Note that the view view_with_unionall satisfies the requirements for fast refresh.

• Each query block in the UNION ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.

  The appropriate materialized view logs must be created on the tables as required for the corresponding type of fast refreshable materialized view.

  Note that the Oracle Database also allows the special case of a single table materialized view with joins only provided the ROWID column has been included in the SELECT list and in the materialized view log. This is shown in the defining query of the view view_with_unionall.

• The SELECT list of each query must include a UNION ALL marker, and the UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block. See "UNION ALL Marker" for more information regarding UNION ALL markers.

• Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with UNION ALL.

• The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.