Creating materialized views

When you create a materialized view, its definition is stored in the database. The database server validates the definition to make sure it compiles properly. All column and table references are fully qualified by the database server to ensure that all users with access to the view see an identical definition. After successfully creating a materialized view, you populate it with data, also known as initializing the view, using a REFRESH MATERIALIZED VIEW statement. See REFRESH MATERIALIZED VIEW statement.

Before creating, initializing, or refreshing materialized views, ensure that all materialized view restrictions have been met. See Restrictions on materialized views.

To obtain a list of all materialized views in the database, including their status, use the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.

After you finish creating the definition for the materialized view, it appears in the Views folder in Sybase Central.

To create a materialized view (Sybase Central)

  1. Connect to the database as a user with DBA or RESOURCE authority.

  2. In the left pane, right-click Views and choose New » Materialized View.

  3. Follow the instructions in the Create Materialized View.

  4. Initialize the materialized view so that it contains data. See Initializing materialized views.

To create a materialized view (SQL)

  1. Connect to the database as a user with DBA or RESOURCE authority.

  2. Execute a CREATE MATERIALIZED VIEW statement. The database server creates and stores the view definition in the database, and sets the view's status to ENABLED. See CREATE MATERIALIZED VIEW statement.

  3. You must initialize the materialized view so that it contains data. See Initializing materialized views.

Example

The following statement creates a materialized view, EmployeeConfid16, containing information about employees, and then initializes it to populate it with data. 

CREATE MATERIALIZED VIEW EmployeeConfid16 AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid16;
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