|
Use this statement to alter a materialized view.
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
}
percent-free-space : integer
SET HIDDEN clause Use the SET HIDDEN clause to obfuscate the definition of the materialized view. This setting is irreversible. For more information, see Hiding materialized views.
ENABLE clause Use the ENABLE clause to enable a materialized view, making it available for use by the database server. This clause has no effect on a view that is already enabled. After using this clause, you must execute a REFRESH MATERIALIZED VIEW statement to initialize the materialized view with data.
DISABLE clause Use the DISABLE clause to make the materialized view unavailable for use by the database server. When you disable a materialized view, the database server drops the data and all indexes for the view. The indexes must be reconstructed, and the view refreshed, after you re-enable the view.
{ ENABLE | DISABLE } USE IN OPTIMIZATION clause Use this clause to specify whether you want the materialized view to be available for use by the optimizer. 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 Enabling and disabling 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 of 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 value 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.
When you disable a materialized view, all indexes for it are dropped and must be recreated, if necessary, when the view is re-enabled.
After enabling a materialized view (ENABLE clause), you must execute a REFRESH MATERIALIZED VIEW statement to populate it with data.
After you disable a materialized view (DISABLE clause), it is no longer available for use by the database server for answering queries. Any views dependent on the materialized view are also disabled. The data in the materialized view is discarded; however, the definition for the view remains in the database. The DISABLE clause requires exclusive access not only to the view being disabled, but to any dependent views, since they are disabled too. See Enabling and disabling materialized views.
Table encryption must already be enabled on the database in order to encrypt a materialized view (ENCRYPT clause). The materialized view is then encrypted using the encryption key and algorithm specified at database creation time. See Encrypting and decrypting materialized views.
Must be owner of the materialized view or have DBA authority.
Automatic commit.
SQL/2003 Vendor extension.
The following example encrypts the EmployeeSalary materialized view:
ALTER MATERIALIZED VIEW EmployeeSalary ENCRYPTED;