Use this statement to create a materialized view.
CREATE MATERIALIZED VIEW
[ owner.]materialized-view-name [ ( column-name, ... ) ]
[ IN dbspace-name ]
column-name list Specifies the columns to create in the materialized view. If no column-name list is specified, the column names are set to the columns specified in the select-statement of the AS clause.
IN clause Specifies the dbspace in which to create the materialized view. If not specified, the current dbspace is used.
AS clause Defines the structure of the materialized view using a select-statement. A materialized view definition can only reference base tables. It cannot reference views, other materialized views, or temporary tables. The select-statement must contain column names or have an alias-name specified (see SELECT statement). You cannot use a SELECT * construct to specify column names. For example, you cannot specify
CREATE MATERIALIZED VIEW matview AS SELECT * FROM table-name .... Also, all objects in the select-statement must have unique names in the database.
See Restrictions when managing materialized views.
Materialized views are not automatically initialized with data when created. To initialize a materialized view, use either the REFRESH MATERIALIZED VIEW statement to initialize an individual materialized view, or the sa_refresh_materialized_views system procedure to initialize all uninitialized materialized views in the database. See REFRESH MATERIALIZED VIEW statement, and sa_refresh_materialized_views system procedure.
You can encrypt a materialized view, change its PCTFREE setting, and enable or disable its use by the optimizer. However, you must create the materialized view first, and then use the ALTER MATERIALIZED VIEW to set these options. The default values for these options at creation time are NOT ENCRYPTED, ENABLE USE IN OPTIMIZATION, and the default PCTFREE according to the page size in use for the database (200 bytes for a 4 KB page size, and 100 bytes for a 2 KB page size).
The sa_recompile_views system procedure does not attempt to recompile materialized views.
Several options need to have specific values in order to create a materialized view. See Restrictions when managing materialized views.
You must have RESOURCE authority and SELECT permission on the tables in the materialized view definition. To create a materialized view for another user, you must also have DBA authority.
While executing, the CREATE MATERIALIZED VIEW statement places exclusive locks, without blocking, on all tables referenced by the materialized view. If one of the referenced tables cannot be locked, the statement fails and an error is returned.
SQL/2003 Vendor extension.
The following example creates a materialized view containing confidential information about employees in the SQL Anywhere sample database. You must subsequently execute a REFRESH MATERIALIZED VIEW statement, to initialize the view for use.
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;