Materialized View Refresh Options

When you define a materialized view, you can specify three refresh options: how to
refresh, what type of refresh, and can trusted constraints be used. If unspecified, the
defaults are assumed as ON DEMAND, FORCE, and ENFORCED constraints respectively.
The two refresh execution modes are ON COMMIT and ON DEMAND.

Depending on the materialized view you create, some of the options may not be available. Table describes the refresh modes.



When a materialized view is maintained using the ON COMMIT method, the time
required to complete the commit may be slightly longer than usual. 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.
If you anticipate performing insert, update or delete operations on tables referenced
by a materialized view concurrently with the refresh of that materialized view, and
that materialized view includes joins and aggregation, Oracle recommends you use ON
COMMIT fast refresh rather than ON DEMAND fast refresh.

If you think the materialized view did not refresh, check the alert log or trace file. If a materialized view fails during refresh at COMMIT time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW package after addressing the errors
specified in the trace files. Until this is done, the materialized view will no longer be refreshed automatically at commit time.

You can specify how you want your materialized views to be refreshed from the detail
tables by selecting one of four options: COMPLETE, FAST, FORCE, and NEVER.

Table describes the refresh options.




Whether the fast refresh option is available depends upon the type of materialized
view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine
whether fast refresh is possible.
You can also specify if it is acceptable to use trusted constraints and QUERY_
REWRITE_INTEGRITY = TRUSTED during refresh. Any nonvalidated RELY
constraint is a trusted constraint. For example, nonvalidated foreign key/primary key
relationships, functional dependencies defined in dimensions or a materialized view
in the UNKNOWN state. If query rewrite is enabled during refresh, these can improve the performance of refresh by enabling more performant query rewrites. Any materialized view that can use TRUSTED constraints for refresh is left in a state of
trusted freshness (the UNKNOWN state) after refresh.

This is reflected in the column STALENESS in the view USER_MVIEWS. The column
UNKNOWN_TRUSTED_FD in the same view is also set to Y, which means yes.
You can define this property of the materialized view either during create time by
specifying REFRESH USING TRUSTED [ENFORCED] CONSTRAINTS or by using ALTER
MATERIALIZED VIEW DDL.



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 view 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"
■ 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.


Restrictions on Fast Refresh on Materialized Views with Aggregates
■ They cannot have GROUP BY clauses or 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" on page 8-19.
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.
■ 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 on
page 8-10 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.
■ 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.
■ 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 maintenance column, called a
UNION ALL marker. 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.

Refreshing Nested Materialized Views
A nested materialized view is considered to be fresh as long as its data is synchronized with the data in its detail tables, even if some of its detail tables could be stale materialized views.

You can refresh nested materialized views in two ways: DBMS_MVIEW.REFRESH with
the nested flag set to TRUE and REFRESH_DEPENDENT with the nested flag set to
TRUE on the base tables. If you use DBMS_MVIEW.REFRESH, the entire materialized
view chain is refreshed and the coverage starting from the specified materialized view in top-down fashion. That is, the specified materialized view and all its child
materialized views in the dependency hierarchy are refreshed in order. With DBMS_
MVIEW.REFRESH_DEPENDENT, the entire chain is refreshed from the bottom up. That
is, all the parent materialized views in the dependency hierarchy starting from the
specified table are refreshed in order.

Example 8–7 Example of Refreshing a Nested Materialized View
The following statement shows an example of refreshing a nested materialized view:
DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);
This statement will first refresh all child materialized views of sales_mv and cost_
mv based on the dependency analysis and then refresh the two specified materialized
views.
You can query the STALE_SINCE column in the *_MVIEWS views to find out when a
materialized view became stale.

Posted in: Materialized Views