Changing the refresh type for a materialized view

When you create a materialized view its refresh type is manual. However, you can change it to immediate. To change from manual to immediate, the view must be in an uninitialized state (contain no data). If the view was just created and has not yet been refreshed, it is uninitialized. If it has data in it, you must truncate the data. The view must also have a unique index, and must conform to the restrictions required for an immediate view. See Additional restrictions for immediate views.

An immediate view can be converted to manual at any time by simply changing its refresh type.

Changing a manual view to an immediate view

The following procedures explain how to change a manual view to an immediate view. Before performing one of these procedures, verify the manual view has a unique index and is uninitialized. Then, optionally, check its eligibility for immediate refresh type using the sa_materialized_view_can_be_immediate system procedure. See sa_materialized_view_can_be_immediate system procedure.

To change a manual view to an immediate view (Sybase Central)

  1. Connect to the database as a user with DBA authority, or as owner of the view and all of the tables it references.

  2. In the left pane, double-click Views.

  3. Right-click the materialized view and choose Properties.

  4. In the Refresh Type field, choose Immediate.

  5. Click OK.

To change a manual view to an immediate view (SQL)

  1. Connect to the database as a user with DBA authority, or as owner of the view and all of the tables it references.

  2. Change the refresh type to immediate by executing an ALTER MATERIALIZED VIEW ... IMMEDIATE REFRESH statement.

For an example of how to change a manual view to an immediate view using the ALTER MATERIALIZED VIEW statement, see ALTER MATERIALIZED VIEW statement.

Changing an immediate view to a manual view

The following procedures explain how to change an immediate view to a manual view.

To change an immediate view to a manual view (Sybase Central)

  1. Connect to the database as the owner of the view, or as a user with DBA authority.

  2. In the left pane, double-click Views.

  3. Right-click a materialized view and choose Properties.

  4. In the Refresh Type field, choose Manual.

  5. Click OK.

To change an immediate view to a manual view (SQL)

  1. Connect to the database as the owner of the view, or as a user with DBA authority.

  2. Change the refresh type to manual by executing an ALTER MATERIALIZED VIEW ... MANUAL REFRESH statement.

Example

The following example creates a materialized view and then initializes it. A unique index is then added since immediate views must have a unique index. Since the view must not have data in it when the refresh type is changed, the view is truncated. Finally, the refresh type is changed. 

CREATE MATERIALIZED VIEW EmployeeConfid44 AS
   SELECT EmployeeID, Employees.DepartmentID, 
          SocialSecurityNumber, Salary, ManagerID, 
          Departments.DepartmentName, Departments.DepartmentHeadID
     FROM Employees, Departments
     WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid44;
CREATE UNIQUE INDEX EmployeeIDIdx 
   ON EmployeeConfid44 ( EmployeeID );
TRUNCATE MATERIALIZED VIEW EmployeeConfid44;
ALTER MATERIALIZED VIEW EmployeeConfid44 
   IMMEDIATE REFRESH;

The following statement changes the refresh type back to manual:

ALTER MATERIALIZED VIEW EmployeeConfid44 
   MANUAL REFRESH;
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 Dropping materialized views.

See also
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0