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 Reference » SQL Statements

CREATE LOCAL TEMPORARY TABLE statement Next Page

CREATE MATERIALIZED VIEW statement


Use this statement to create a materialized view.

Syntax

CREATE MATERIALIZED VIEW
[ owner.]materialized-view-name [ ( column-name, ... ) ]
[ IN dbspace-name ]
AS select-statement

Parameters

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.

Remarks

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.

Permissions

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.

Side effects

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.

See also
Standards and compatibility
Example

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;