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 Usage » Working with Database Objects » Working with views » Working with materialized views

Restrictions when managing materialized views Next Page

Creating materialized views


When creating a materialized view, you first create and store its definition, or schema, 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.

Before creating, initializing, or refreshing materialized views, ensure that all materialized view restrictions have been met. See Restrictions when managing 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, its definition is stored in the database, and the new materialized view appears in the Views folder. However, there is no data in the materialized view. If you want the materialized view to be populated with data, you must initialize it. See Initializing materialized views.

To create a new materialized view (Sybase Central)
  1. Connect to the database with DBA or RESOURCE privileges.

  2. Open the Views folder.

  3. From the File menu, choose New > Materialized View.

    The Create Materialized View wizard appears.

  4. Follow the instructions in the wizard.

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

To create a materialized view (SQL)
  1. Connect to the database with DBA or RESOURCE privileges.

  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.

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

Example

The following statement creates a materialized view, EmployeeConfidential, containing confidential information about employees.

CREATE MATERIALIZED VIEW EmployeeConfidential AS
   SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID,
      Departments.DepartmentName, Departments.DepartmentHeadID
   FROM Employees, Departments
   WHERE Employees.DepartmentID=Departments.DepartmentID
   ORDER BY Employees.DepartmentID;
See also