Purpose

To modify an existing materialized view in one or more of the following ways:

To change its storage characteristics

To change its refresh method, mode, or time

To alter its structure so that it is a different type of materialized view

To enable or disable query rewrite.

The terms snapshot and materialized view are synonymous in Oracle documentation. "Materialized view" is used in this reference. Both refer to a database object that contains the results of a query of one or more tables.

The tables in the query are called master tables (a replication term) or detail tables (a data warehouse term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

Prerequisites

The privileges required to alter a materialized view should be granted directly, as follows:

The materialized view must be in your own schema, or you must have the ALTERANYSNAPSHOT or ALTERANYMATERIALIZEDVIEW system privilege.

To enable a materialized view for query rewrite:

If all of the master tables in the materialized view are in your schema, you must have the QUERYREWRITE privilege.

If any of the master tables are in another schema, you must have the GLOBALQUERYREWRITE privilege.

If the materialized view is in another user's schema, both you and the owner of that schema must have the appropriate QUERYREWRITE privilege, as described in the preceding two items. In addition, the owner of the materialized view must have SELECT access to any master tables that the materialized view owner does not own.

Keywords and Parameters

is the schema containing the materialized view. If you omit schema, Oracle assumes the materialized view is in your own schema.

materialized view / snapshot

is the name of the materialized view to be altered.

physical_attributes_clause

changes values for the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters (or, when used in the USINGINDEX clause, for the INITRANS and MAXTRANS parameters only) and the storage characteristics for the materialized view.

See Also:

"ALTER TABLE" for information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters.

"storage_clause" for information about storage characteristics.

LOGGING| NOLOGGING

establishes or changes the logging characteristics of the materialized view.

For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

See Also:"ALTER TABLE" for information about specifying CACHE or NOCACHE.

LOB_storage_clause

specifies the LOB storage characteristics.

See Also:"ALTER TABLE" for information about specifying the parameters of this clause.

modify_LOB_storage_clause

modifies the physical attributes of the LOB attribute lob_item or LOB object attribute.

See Also:"ALTER TABLE" for information about specifying the parameters of this clause.

partitioning_clauses:

The syntax and general functioning of the partitioning clauses is the same as for partitioned tables, as described in "ALTER TABLE".

Restrictions:

You cannot use the LOB_storage_clause or modify_LOB_storage_clause when modifying a materialized view.

If you attempt to drop, truncate, or exchange a materialized view partition, Oracle raises an error.

Note: If you wish to keep the contents of the materialized view synchronized with those of the master table, Oracle Corporation recommends that you manually perform a complete refresh of all materialized views dependent on the table after dropping or truncating a table partition.

parallel_clause

changes the default degree of parallelism for the materialized view.

NOPARALLEL

specifies serial execution.

PARALLEL

causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

PARALLELinteger

specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

marks UNUSABLE all the local index partitions associated with partition.

MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES

rebuilds the unusable local index partitions associated with partition.

USING INDEX

changes the value of INITRANS, MAXTRANS, and STORAGE parameters for the index Oracle uses to maintain the materialized view's data.

Restriction: You cannot specify the PCTUSED or PCTFREE parameters in this clause.

refresh_clause

changes the default method and mode and the default times for automatic refreshes. If the contents of a materialized view's master tables are modified, the data in the materialized view must be updated to make the materialized view accurately reflect the data currently in its master table(s). This clause lets you schedule the times and specify the method and mode for Oracle to refresh the materialized view.

specifies the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes are stored either in the materialized view log associated with the master table (for conventional DML changes) or in the direct loader log (for direct-load INSERTs).

For both conventional DML changes and for direct-path loads, other conditions may restrict the eligibility of a materialized view for fast refresh.

When you specify FAST refresh at create time, Oracle verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to FAST in an ALTERMATERIALIZEDVIEW statement, Oracle does not perform this verification. If the materialized view is not eligible for fast refresh, Oracle will return an error when you attempt to refresh this view.

Materialized views are not eligible for fast refresh if the defining query contains an analytic function.

specifies the complete refresh method, which is implemented by executing the materialized view's defining query. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

FORCE

specifies that when a refresh occurs, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise.

ON COMMIT

specifies that a fast refresh is to occur whenever Oracle commits a transaction that operates on a master table of the materialized view.

Restriction: This clause is supported only for materialized join views and single-table materialized aggregate views.

If you specify ONCOMMIT or ONDEMAND, you cannot also specify STARTWITH or NEXT.

START WITH

specifies a date expression for the first automatic refresh time.

NEXT

specifies a date expression for calculating the interval between automatic refreshes.

Both the STARTWITH and NEXT values must evaluate to a time in the future. If you omit the STARTWITH value, Oracle determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a STARTWITH value but omit the NEXT value, Oracle refreshes the materialized view only once. If you omit both the STARTWITH and NEXT values, or if you omit the refresh_clause entirely, Oracle does not automatically refresh the materialized view.

changes the remote rollback segment to be used during materialized view refresh, where rollback_segment is the name of the rollback segment to be used.

See Also:Oracle8i Replication for information on changing the local materialized view rollback segment using the DBMS_REFRESH package.

DEFAULT specifies that Oracle will choose the rollback segment to use. If you specify DEFAULT, you cannot specify rollback_segment.

MASTER specifies the remote rollback segment to be used at the remote master for the individual materialized view. (To change the local materialized view rollback segment, use the DBMS_REFRESH package, described in Oracle8i Replication.)

The master rollback segment is stored on a per-materialized-view basis and is validated during materialized view creation and refresh. If the materialized view is complex, the master rollback segment, if specified, is ignored.

QUERY REWRITE

specifies whether the materialized view is eligible to be used for query rewrite.

specifies that the materialized view is not eligible for use by query rewrite. (If a materialized view is in the invalid state, it is not eligible for use by query rewrite, whether or not it is disabled.) However, a disabled materialized view can be refreshed.

COMPILE

explicitly revalidates a materialized view. If an object upon which the materialized view depends is dropped or altered, the materialized view remains accessible, but it is invalid for query rewrite. You can use this clause to explicitly revalidate the materialized view to make it eligible for query rewrite.

If the materialized view fails to revalidate, it cannot be refreshed or used for query rewrite.

CONSIDER FRESH

directs Oracle to consider the materialized view fresh and therefore eligible for query rewrite in the TRUSTED or STALE_TOLERATED modes. Because Oracle cannot guarantee the freshness of the materialized view, query rewrite in ENFORCED mode is not supported. This clause also sets the staleness state of the materialized view to UNKNOWN. The staleness state is displayed in the STALENESS column of the ALL_MVIEWS, DBA_MVIEWS, and USER_MVIEWS data dictionary views.

This clause is useful after performing partition maintenance operations against the master table. Such operations would otherwise render the materialized view ineligible for fast refresh, and eligible for query rewrite only in STALE_TOLERATED mode.

Note: A materialized view is stale if changes have been made to the contents of any of its master tables. This clause directs Oracle to assume that the materialized view is fresh and that no such changes have been made. Therefore, actual updates to those tables pending refresh are purged with respect to the materialized view.

See Also: Oracle8i Data Warehousing Guide for more information on query rewrite and the implications of performing partition maintenance operations on master tables.

Examples

Automatic Refresh Example

The following statement changes the default refresh method for the HQ_EMP materialized view to FAST:

The next automatic refresh of the materialized view will be a fast refresh provided it is a simple materialized view and its master table has a materialized view log that was created before the materialized view was created or last refreshed.

Because the REFRESH clause does not specify STARTWITH or NEXT values, the refresh intervals established by the REFRESH clause when the HQ_EMP materialized view was created or last altered are still used.

NEXT Example

The following statement stores a new interval between automatic refreshes for the BRANCH_EMP materialized view:

ALTER MATERIALIZED VIEW branch_emp
REFRESH NEXT SYSDATE+7;

Because the REFRESH clause does not specify a STARTWITH value, the next automatic refresh occurs at the time established by the STARTWITH and NEXT values specified when the BRANCH_EMP materialized view was created or last altered.

At the time of the next automatic refresh, Oracle refreshes the materialized view, evaluates the NEXT expression SYSDATE+7 to determine the next automatic refresh time, and continues to refresh the materialized view automatically once a week.

Because the REFRESH clause does not explicitly specify a refresh method, Oracle continues to use the refresh method specified by the REFRESH clause of the CREATEMATERIALIZEDVIEW or most recent ALTERMATERIALIZEDVIEW statement.

Complete Refresh Example

The following statement specifies a new refresh method, a new next refresh time, and a new interval between automatic refreshes of the SF_EMP materialized view:

The STARTWITH value establishes the next automatic refresh for the materialized view to be 9:00 a.m. tomorrow. At that point, Oracle performs a complete refresh of the materialized view, evaluates the NEXT expression, and subsequently refreshes the materialized view every week.

Enabling Query Rewrite Example

The following statement enables query rewrite on the materialized view MV1 and implicitly revalidates it.

ALTER MATERIALIZED VIEW mv1
ENABLE QUERY REWRITE;

Rollback Segment Examples

The following statement changes the remote master rollback segment used during materialized view refresh to MASTER_SEG:

Primary Key Example

The following statement changes a rowid materialized view to a primary key materialized view:

ALTER MATERIALIZED VIEW emp_rs
REFRESH WITH PRIMARY KEY;

COMPILE Example

The following statement revalidates the materialized view STORE_MV:

ALTER MATERIALIZED VIEW store_mv COMPILE;

Modifying Refresh Method Example

The following statement changes the refresh method of materialized view STORE_MV to FAST;

ALTER MATERIALIZED VIEW store_mv REFRESH FAST;

CONSIDER FRESH Example

The following statement instructs Oracle that materialized view MV1 should be considered fresh. This statement allows MV1 to be eligible for query rewrite in TRUSTED mode even after you have performed partition maintenance operations on the master tables of MV1: