|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
ALTER CLUSTER to ALTER SEQUENCE, 9 of 18
A materialized view is a database object that contains the results of a query. The
FROM clause of the query can name tables, views, and other materialized views. Collectively these 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.
VIEW statement to modify an existing materialized view in one or more of the following ways:
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
VIEW system privilege.
To enable a materialized view for query rewrite:
REWRITEprivilege, as described in the preceding two items. In addition, the owner of the materialized view must have
SELECTaccess to any master tables that the materialized view owner does not own.
mapping_table_clauses: not supported with materialized views
compression_clauses: not supported with materialized views
alter_mapping_clauses: not supported with materialized views
Specify the schema containing the materialized view. If you omit
schema, Oracle assumes the materialized view is in your own schema.
Specify the name of the materialized view to be altered.
Specify new values for the
MAXTRANS parameters (or, when used in the
INDEX clause, for the
MAXTRANS parameters only) and the storage characteristics for the materialized view.
LOB_storage_clause lets you specify the LOB storage characteristics.
modify_LOB_storage_clause lets you modify the physical attributes of the LOB attribute
lob_item or LOB object attribute.
The syntax and general functioning of the partitioning clauses for materialized views is the same as for partitioned tables.
Restrictions on partitioning_clauses:
modify_LOB_storage_clausewithin any of the
Use this clause to mark
UNUSABLE all the local index partitions associated with
Use this clause to rebuild the unusable local index partitions associated with
parallel_clause lets you change the default degree of parallelism for the materialized view.
NOPARALLEL for serial execution. This is the default.
PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the
PARALLEL_THREADS_PER_CPU initialization parameter.
integer indicates 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 servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify
"Notes on the parallel_clause" for
Specify or change the logging characteristics of the materialized view.
allocate_extent_clause lets you explicitly allocate a new extent for 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.
alter_iot_clauses to change the characteristics of an index-organized materialized view. The keywords and parameters of the components of the
alter_iot_clauses have the same semantics as in
TABLE, with the restrictions that follow.
Restrictions: You cannot specify the
mapping_table_clauses or the
compression_clauses of the
"ORGANIZATION INDEX Clause" of
Use this clause to change the value of
STORAGE parameters for the index Oracle uses to maintain the materialized view's data.
Restriction: You cannot specify the
PCTFREE parameters in this clause.
scoped_table_ref_constraint clause to rescope a
REF column or attribute to a new table.
Restrictions: You can rescope only one
REF column or attribute in each
VIEW statement, and this must be the only clause in this statement.
REBUILD to regenerate refresh operations if a type that is referenced in
materialized_view has evolved.
Restriction: You cannot specify any other clause in the same
alter_mv_refresh_clause to change 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.
FAST for 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-path
For both conventional DML changes and for direct-path
INSERTs, other conditions may restrict the eligibility of a materialized view for fast refresh.
FASTrefresh at create time, Oracle verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to
VIEWstatement, 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.
COMPLETE for 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 if, when a refresh occurs, you want Oracle to perform a fast refresh if one is possible or a complete refresh otherwise.
COMMIT if you want a fast refresh 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.
DEMAND if you want the materialized view to be refreshed on demand by calling one of the three
DBMS_MVIEW refresh procedures. If you omit both
DEMAND is the default.
date to indicate a date for the first automatic refresh time.
NEXT to indicate a date expression for calculating the interval between automatic refreshes.
NEXT values must evaluate to a time in the future. If you omit the
WITH 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
WITH value but omit the
NEXT value, Oracle refreshes the materialized view only once. If you omit both the
NEXT values, or if you omit the
alter_mv_refresh_clause entirely, Oracle does not automatically refresh the materialized view.
KEY to change a rowid materialized view to a primary key materialized view. Primary key materialized views allow materialized view master tables to be reorganized without affecting the materialized view's ability to continue to fast refresh. The master table must contain an enabled primary key constraint.
Oracle9i Replication for detailed information about primary key materialized views
SEGMENT to change the remote rollback segment to be used during materialized view refresh, where
rollback_segment is the name of the rollback segment to be used.
Oracle9i Replication for information on changing the local materialized view rollback segment using the
DEFAULT if you want Oracle to choose the rollback segment to use. If you specify
DEFAULT, you cannot specify
Specify 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 Oracle9i Replication.)
One master rollback segment is stored for each materialized view and is validated during materialized view creation and refresh. If the materialized view is complex, the master rollback segment, if specified, is ignored.
Use this clause to determine whether the materialized view is eligible to be used for query rewrite.
ENABLE to enable the materialized view for query rewrite.
DISABLE if you do not want the materialized view to be 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 to explicitly revalidate 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.
This clause lets you manage the staleness state of a materialized after changes have been made to its master tables.
FRESH directs Oracle to consider the materialized view fresh and therefore eligible for query rewrite in the
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
USER_MVIEWS data dictionary views.
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.
Oracle9i Data Warehousing Guide for more information on query rewrite and the implications of performing partition maintenance operations on master tables
The following statement changes the default refresh method for the
sales_by_month_by_state materialized view (created in "Materialized Aggregate View Examples") to
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.
REFRESH clause does not specify
NEXT values, Oracle will use the refresh intervals established by the
REFRESH clause when the
sales_by_month_by_state materialized view was created or last altered.
The following statement stores a new interval between automatic refreshes for the
branch_emp materialized view:
REFRESH clause does not specify a
WITH value, the next automatic refresh occurs at the time established by the
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
SYSDATE+7 to determine the next automatic refresh time, and continues to refresh the materialized view automatically once a week.
REFRESH clause does not explicitly specify a refresh method, Oracle continues to use the refresh method specified by the
REFRESH clause of the
VIEW or most recent
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:
WITH 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.
The following statement enables query rewrite on the materialized view
mv1 and implicitly revalidates it.
The following statement changes the remote master rollback segment used during materialized view refresh to
The following statement changes the remote master rollback segment used during materialized view refresh to one chosen by Oracle:
The following statement changes a rowid materialized view to a primary key materialized view:
The following statement revalidates the materialized view
The following statement changes the refresh method of materialized view
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