ALTER MATERIALIZED VIEW statement

Use this statement to alter a materialized view.

Syntax

ALTER MATERIALIZED VIEW [ owner.]materialized-view-name { 
 SET HIDDEN
| { ENABLE | DISABLE }
| { ENABLE | DISABLE } USE IN OPTIMIZATION 
| { ADD PCTFREE percent-free-space | DROP PCTFREE }
| [ NOT ] ENCRYPTED
[ { IMMEDIATE | MANUAL } REFRESH ]
}

percent-free-space : integer

Parameters

SET HIDDEN clause Use the SET HIDDEN clause to obfuscate the definition of a materialized view. This setting is irreversible. For more information, see Hide materialized views.
ENABLE clause Use the ENABLE clause to enable a disabled materialized view, making it available for the database server to use. This clause has no effect on a view that is already enabled. After using this clause, you must refresh the view to initialize it, and recreate any text indexes that were dropped when the view was disabled.
DISABLE clause Use the DISABLE clause to disable use of the view by the database server. When you disable a materialized view, the database server drops the data and indexes for the view.
{ ENABLE | DISABLE } USE IN OPTIMIZATION clause Use this clause to specify whether you want the materialized view to be available the optimizer to use. If you specify DISABLE USE IN OPTIMIZATION, the materialized view is used only when executing queries that explicitly reference the view. The default is ENABLE USE IN OPTIMIZATION. See Enable and disable optimizer use of a materialized view.
ADD PCTFREE clause Specify the percentage of free space you want to reserve on each page. The free space is used if rows increase in size when the data is updated. If there is no free space on a page, every increase in the size of a row on that page requires the row to be split across multiple pages, causing row fragmentation and possible performance degradation.
The value of percent-free-space is an integer between 0 and 100. The value 0 specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, or is dropped, the default PCTFREE setting is applied according to the database page size (200 bytes for a 4 KB page size, and 100 bytes for a 2 KB page size).
DROP PCTFREE clause Removes the PCTFREE setting currently in effect for the materialized view, and applies the default PCTFREE according to the database page size.
[ NOT ] ENCRYPTED clause Specify whether to encrypt the materialized view data. By default, materialized view data is not encrypted at creation time. To encrypt a materialized view, specify ENCRYPTED. To decrypt a materialized view, specify NOT ENCRYPTED.
REFRESH clause Use the REFRESH clause to change the refresh type for the materialized view:
- IMMEDIATE REFRESH Use the IMMEDIATE REFRESH clause to change a manual view to an immediate view. The manual view must be valid and uninitialized to change the refresh type to IMMEDIATE REFRESH. If the view is in an initialized state, execute a TRUNCATE statement to change the state to uninitialized before executing the ALTER MATERIALIZED VIEW...IMMEDIATE REFRESH. See TRUNCATE statement.
  For information about conditions that must be met before you can alter the view to IMMEDIATE REFRESH, see Additional restrictions for immediate views.
- MANUAL REFRESH Use the MANUAL REFRESH clause to change an immediate view to a manual view.
For more information about refresh types, see Manual and immediate materialized views.

For more information about statuses, see Materialized view statuses and properties.

Remarks

If you alter a materialized view owned by another user, you must qualify the name by including the owner (for example, GROUPO.EmployeeConfidential). If you don't qualify the name, the database server looks for a materialized view with that name owned by you and alters it. If there isn't one, it returns an error.

When you disable a materialized view (DISABLE clause), it is no longer available for the database server to use for answering queries. As well, the data and indexes are dropped, and the refresh type changes to manual. Any dependent regular views are also disabled.

The DISABLE clause requires exclusive access not only to the view being disabled, but to any dependent views, since they are also disabled. See Enable and disable materialized views.

Table encryption must already be enabled on the database to encrypt a materialized view (ENCRYPTED clause). The materialized view is then encrypted using the encryption key and algorithm specified at database creation time. See Encrypt and decrypt materialized views.

Permissions

To execute the ALTER MATERIALIZED VIEW statement you must own the view or have DBA authority.

If you do not have DBA authority but want to alter a materialized view to be immediate (ALTER MATERIALIZED VIEW ... IMMEDIATE REFRESH), you must own the view and all the tables it references.

The only operations a user can perform on a materialized view to change its data are refreshing, truncating, and disabling. However, immediate views are automatically updated by the database server. That is, once an immediate view is enabled and initialized, the database server maintains it automatically, without additional permissions checking.

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following statements creates the EmployeeConfid88 materialized view and then disables its use in optimization:

CREATE MATERIALIZED VIEW EmployeeConfid88 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid88;
ALTER MATERIALIZED VIEW GROUPO.EmployeeConfid88 DISABLE USE IN OPTIMIZATION;

Caution

When you are done with this example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables Employees and Departments, when trying out other examples. You cannot alter the schema of a table that has enabled, dependent materialized view. See Drop materialized views.

Discuss this page in DocCommentXchange.
Send feedback about this page using email.