Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 8 of 17
LOB_storage_clause: See "ALTER TABLE".
modify_LOB_storage_clause: See "ALTER TABLE".
partitioning_clauses: See "ALTER TABLE".
storage_clause: See the "storage_clause".
To modify an existing materialized view in one or more of the following ways:
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.
See Also:
|
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 ALTER
ANY
SNAPSHOT
or ALTER
ANY
MATERIALIZED
VIEW
system privilege.
To enable a materialized view for query rewrite:
QUERY
REWRITE
privilege.
GLOBAL
QUERY
REWRITE
privilege.
QUERY
REWRITE
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.
schema |
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 |
|
|
See Also:
|
|
|
establishes or changes the logging characteristics of the materialized view. See Also: "ALTER TABLE" for information about logging characteristics. |
|
allocate_extent_clause |
explicitly allocates a new extent for the materialized view. See Also: "ALTER TABLE". |
|
|
For data that will be accessed frequently,
See Also: "ALTER TABLE" for information about specifying |
|
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: |
|
|
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. |
|
|
|
specifies serial execution. |
|
|
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 |
|
|
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. |
|
See Also: The Notes to the parallel_clause of "CREATE TABLE". |
|
|
||
|
marks |
|
|
||
|
rebuilds the unusable local index partitions associated with partition. |
|
|
changes the value of
Restriction: You cannot specify the |
|
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. |
|
|
Note: This clause only sets the default refresh options. For instructions on actually implementing the refresh, refer to Oracle8i Replication and Oracle8i Data Warehousing Guide. |
|
|
|
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. See Also:
|
|
|
Notes:
|
|
|
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. |
|
|
specifies that when a refresh occurs, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. |
|
|
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. See Also: Oracle8i Replication and Oracle8i Data Warehousing Guide. |
|
|
specifies that the materialized view will be refreshed on demand by calling one of the three See Also:
|
|
If you specify |
|
|
|
specifies a date expression for the first automatic refresh time. |
|
|
specifies a date expression for calculating the interval between automatic refreshes. |
|
Both the |
|
|
|
changes 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. See Also: Oracle8i Replication for detailed information about primary key materialized views. |
|
|
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 |
|
|
|
|
|
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. |
|
specifies whether the materialized view is eligible to be used for query rewrite. |
|
|
|
enables the materialized view for query rewrite. See Also: Oracle8i Data Warehousing Guide for more information on query rewrite. |
|
|
Restrictions:
|
|
|
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. |
|
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. |
|
|
directs Oracle to consider the materialized view fresh and therefore eligible for query rewrite in the
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 |
|
|
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. |
The following statement changes the default refresh method for the HQ_EMP
materialized view to FAST
:
CREATE MATERIALIZED VIEW hq_emp REFRESH COMPLETE START WTIH SYSDATE NEXT SYSDATE +1/4096 AS SELECT * FROM hq_emp; ALTER MATERIALIZED VIEW hq_emp REFRESH 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 START
WITH
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.
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 START
WITH
value, the next automatic refresh occurs at the time established by the START
WITH
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 CREATE
MATERIALIZED
VIEW
or most recent ALTER
MATERIALIZED
VIEW
statement.
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:
ALTER MATERIALIZED VIEW sf_emp REFRESH COMPLETE START WITH TRUNC(SYSDATE+1) + 9/24 NEXT SYSDATE+7;
The START
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.
ALTER MATERIALIZED VIEW mv1 ENABLE QUERY REWRITE;
The following statement changes the remote master rollback segment used during materialized view refresh to MASTER_SEG
:
ALTER MATERIALIZED VIEW inventory REFRESH USING MASTER ROLLBACK SEGMENT master_seg;
The following statement changes the remote master rollback segment used during materialized view refresh to one chosen by Oracle:
ALTER MATERIALIZED VIEW sales REFRESH USING DEFAULT MASTER ROLLBACK SEGMENT;
The following statement changes a rowid materialized view to a primary key materialized view:
ALTER MATERIALIZED VIEW emp_rs REFRESH WITH PRIMARY KEY;
The following statement revalidates the materialized view STORE_MV
:
ALTER MATERIALIZED VIEW store_mv COMPILE;
The following statement changes the refresh method of materialized view STORE_MV
to FAST
;
ALTER MATERIALIZED VIEW store_mv REFRESH FAST;
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
:
ALTER MATERIALIZED VIEW mv1 CONSIDER FRESH;
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|