CREATE MATERIALIZED VIEW statement

Use this statement to create a materialized view.

Syntax 
CREATE MATERIALIZED VIEW 
[ owner.]materialized-view-name [ ( alt-column-names, ... ) ]
[ IN dbspace-name ]
AS select-statement
[ CHECK { IMMEDIATE | MANUAL } REFRESH ]

alt-column-names :
( column-name [,...] )
Parameters

  • alt-column-names   Use this clause to specify alternate names for the columns in the materialized view. If you specify alternate columns names, the number of columns listed in alt-column-names must match the number of columns in select-statement. If you do not specify alternate column names, the names are set to those in select-statement.

  • IN clause   Use this clause to specify the dbspace in which to create the materialized view. If this clause is not specified, then the materialized view is created in the dbspace specified by the default_dbspace option. Otherwise, the system dbspace is used. For more information, see Using additional dbspaces.

  • AS clause   Use this clause to specify, in the form of a SELECT statement, the data to use to populate the materialized view. A materialized view definition can only reference base tables, it cannot reference views, other materialized views, or temporary tables. select-statement must contain column names or have an alias name specified. If you specify alt-column-names, those names are used instead of the aliases specified in select-statement.

    Column names in the SELECT statement must be specified explicitly; you cannot use the SELECT * construct. For example, you cannot specify CREATE MATERIALIZED VIEW matview AS SELECT * FROM table-name. Also, you should fully qualify objects names in the select-statement. See Restrictions on materialized views.

  • CHECK clause   Use this clause to validate the statement without actually creating the view. When you specify the CHECK clause:

    • The database server performs the normal language checks that would be carried out if CREATE MATERIALIZED VIEW was executed without the clause, and any errors generated are returned as usual.

    • The database server does not carry out the actual creation of the view. This means that certain errors that would occur at creation time are not generated. For example, an error indicating that the specified view name already exists is not generated. This allows you to use the CHECK clause to test intended changes to the definition of the view, without a conflict with the naming of the view.

    • If CHECK IMMEDIATE REFRESH is used then the database server verifies that the syntax is valid for an immediate view and returns any errors.

    • No changes are made to the database, and nothing is recorded in the transaction log.

    • There is an implicit commit at the beginning of statement execution and a rollback at the end to release all locks obtained during execution.

Remarks

When you create a materialized view, it is a manual view and uninitialized. That is, it has a manual refresh type, and it has not been refreshed (populated with data). To initialize the view, execute a REFRESH MATERIALIZED VIEW statement, or use the sa_refresh_materialized_views system procedure. See REFRESH MATERIALIZED VIEW statement, and sa_refresh_materialized_views system procedure.

You can encrypt a materialized view, change its PCTFREE setting, change its refresh type, 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 change these settings. The default values for materialized views at creation time are:

  • NOT ENCRYPTED

  • ENABLE USE IN OPTIMIZATION

  • PCTFREE is set according to the database page size: 200 bytes for a 4 KB page size, and 100 bytes for a 2 KB page size.

  • MANUAL REFRESH

Several database and server options must be in effect to create a materialized view. See Restrictions on materialized views.

The sa_recompile_views system procedure does not affect 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

  • SQL/2003   Vendor extension.

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, as shown in the example. 

CREATE MATERIALIZED VIEW EmployeeConfid2 AS
SELECT EmployeeID, Employees.DepartmentID, 
    SocialSecurityNumber, Salary, ManagerID, 
    Departments.DepartmentName, Departments.DepartmentHeadID
FROM Employees, Departments
WHERE Employees.DepartmentID=Departments.DepartmentID;
REFRESH MATERIALIZED VIEW EmployeeConfid2;
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1