Create materialized views

When you use SQL to create a materialized view from a base table, the system stores the view definition for the lifetime of the SPM view and the view is visible as a materialized view. Records that are associated with the SPM view are materialized into a unique table. SPM data slices are co-located on the same data slices as the corresponding base table data slices and persistently stored on the user disk partitions.

To create a materialized view, enter:
   MYDB.SCHEMA(USER)=> CREATE MATERIALIZED VIEW customers_mview AS SELECT 
customer_name, customer_id FROM customers ORDER BY customer_id;
The following restrictions apply to creating a materialized view:
  • You can only specify one base table in the FROM clause.
  • You cannot use the WHERE clause.
  • The columns in the projection list must be columns in the base table and no expressions (aggregates, mathematical operators, casting, DISTINCT operator, and other expressions) are allowed.
  • You must specify at least one column in the projection list.
  • The columns in the optional ORDER BY clause must be one or more columns in the projection list. If you do not specify ORDER BY, the materialized view retains the same sort order as the base table.
  • You cannot specify NULLS LAST or DESC in the ORDER BY expression.
  • You cannot specify an external, temporary, system, or a clustered base table (CBT) as a base table for the view.
Note: As you insert new records into the base table that has an associated SPM view, the system appends the new records to the materialized view table as well. Thus, there are two areas in the materialized table: one area contains the sorted records that are generated when the view was created, the other area contains unsorted records that are inserted into the base table subsequent to the SPM view creation. If your query performance depends on the sorted nature of the SPM view, manually refresh the SPM view by suspending and refreshing it periodically.