Alters a materialized view.
SyntaxALTER 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
ParametersSET HIDDEN clause Use the SET HIDDEN clause to obfuscate the definition of a materialized view. This setting is irreversible.
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 for 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.
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.
MANUAL REFRESH Use the MANUAL REFRESH clause to change an immediate view to a manual view.
RemarksIf 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.
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.
PermissionsTo 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 effectsAutomatic commit.
See also Standards and compatibilitySQL/2008 Vendor extension.
ExampleThe 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; |
When you are done with this example, you should drop the materialized view you created. Otherwise, you cannot 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.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |