物化视图快速刷新的限制

郑全

General Restrictions on Fast Refresh

•Thematerialized view must not contain references to nonrepeatingexpressions such as SYSDATE and ROWNUM.

•Thematerialized view must not contain references to RAW or LONG RAW datatypes.

•It cannotcontain a SELECT list subquery.

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

•It cannotcontain a MODEL clause.

•It cannotcontain a HAVING clausewith a subquery.

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

•It cannotcontain a [STARTWITH …] CONNECT BYclause.

•It cannotcontain multiple detail tables at different sites.

•ON COMMITmaterialized views cannot have remote detail tables.

•Nestedmaterialized views must have a join or aggregate.

Restrictions on Fast Refresh of Materialized Join Views

•Allgeneral restrictions apply.

•Theycannot have GROUPBYclauses or aggregates.

•Rowids of allthe tables in the FROM listmust appear in the SELECT list ofthe query.

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

•Youcannot create a fast refreshable materialized view from multiple tables withsimple joins that include an object type column in the SELECT statement.

Restrictions on Fast Refresh of Materialized Views withAggregates (MAV’s)

•Allgeneral restrictions apply.

•Fastrefresh is supported for both the ON COMMIT and ON DEMAND refreshmethods.

•Alltables in the materialized view must have materialized view logs specifiedcorrectly.

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

•COUNT(*) must bespecified.

•Aggregatefunctions must occur only as the outermost part of the expression. That is,aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are notallowed.

•For eachaggregate such as AVG(expr), the corresponding COUNT(expr) must be present. It is recommendedthat SUM(expr) be specified.

•If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. It is recommendedthat SUM(expr *expr)be specified.

•The SELECT column in the defining query cannotbe a complex expression with columns from multiple base tables. A possibleworkaround to this is to use a nested materialized view.

The SELECT list must contain all GROUP BY columns.

•If thematerialized view has one of the following, fast refresh is supported only onconventional data manipulation language (DML) inserts and direct loads.

-Materializedviews with MIN or MAX aggregates

-Materializedviews that have SUM(expr) but no COUNT(expr)

-Materializedviews without COUNT(*)

•Amaterialized view with MAX or MIN is fast refreshable after delete ormixed DML statements if it does not have a WHERE clause.

     • Materializedviews with named views or subqueries in the FROM clause can be fast refreshedprovided the views can be completely merged.
     • If thereare no outer joins, you may have arbitrary selections and joins in the WHERE clause.
     • Materializedaggregate views with outer joins are fast refreshable after conventional DMLand direct loads, provided only the outer table has been modified.
     • Uniqueconstraints must exist on the join columns of the inner join table.

•All thejoins must be connected by ANDs and must use the equality (=) operator.

•Formaterialized views with CUBE, ROLLUP, grouping sets, or concatenation ofthem, the following restrictions apply:

-The SELECT list should contain a groupingdistinguisher that can either be a GROUPING_ID function on all GROUP BYexpressions or GROUPINGfunctions one for each GROUPBYexpression.

-GROUP BY shouldnot result in any duplicate groupings.

Restrictions on Fast Refresh with UNION ALL

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

•Eachquery block in the UNIONALLquery must satisfy the requirements of a fast refreshable materialized viewwith aggregates or a fast refreshable materialized view with joins.

•The SELECT list of each query must include a UNION ALL marker,and the UNIONALLcolumn must have a distinct constant numeric or string value in each UNION ALL branch.

Somefeatures such as outer joins, insert-only aggregate materialized view queriesand remote tables are not supported for materialized views with UNION ALL.