Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

ALTER INDEX statement Next Page


Use this statement to alter a materialized view.


ALTER MATERIALIZED VIEW [ owner.]materialized-view-name {
| { ADD PCTFREE percent-free-space | DROP PCTFREE }

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.

Side effects

Automatic commit.

See also
Standards and compatibility

The following example encrypts the EmployeeSalary materialized view: