CREATE MATERIALIZED VIEW

The CREATE MATERIALIZED VIEW statement creates a view of the table specified in the SelectQuery clause. The original tables used to create a view are referred to as "detail" tables. The view can be refreshed synchronously or asynchronously with regard to changes in the detail tables. If you create an asynchronous materialized view, you must first create a materialized view log on the detail table. See "CREATE MATERIALIZED VIEW LOG".

Required privilege

• CREATE MATERIALIZED VIEW (if owner) or CREATE ANY MATERIALIZED VIEW (if not owner) and

• SELECT on the detail tables and

• CREATE TABLE (if owner) or CREATE ANY TABLE (if not owner)

SQL syntax

CREATE MATERIALIZED VIEW ViewName
      [REFRESH 
        [FAST | COMPLETE]
        [NEXT SYSDATE[+NUMTODSINTERVAL(IntegerLiteral),IntervalUnit]]
      AS SelectQuery
      [PRIMARY KEY (ColumnName [,…])] 
      [UNIQUE HASH ON (HashColumnName [,…]) 
      PAGES = PrimaryPages]

Parameters

The CREATE MATERIALIZED VIEW statement has the parameters:

Description

Restrictions on synchronous materialized view and detail tables:

• A materialized view is read-only and cannot be updated directly. A materialized view is updated only when changes are made to the associated detail tables. Therefore a materialized view cannot be the target of a DELETE, UPDATE or INSERT statement.

• Materialized views defined on replicated tables may result in replication failures or inconsistencies if the materialized view is specified so that overflow or underflow conditions occur when the materialized view is updated.

• Detail tables can be replicated, but materialized views themselves cannot be replicated. If detail tables are replicated, TimesTen automatically updates the corresponding views.

• A materialized view and its detail tables cannot be part of a cache group.

• Do not create a materialized view with the same name as a sequence.

• Referential constraints cannot be defined on materialized views.

• By default, a range index is created to enforce the primary key for a materialized view. Use the UNIQUE HASH clause to specify a hash index for the primary key.

  • If your application performs range queries over a materialized view's primary key, then choose a range index for that view by omitting the UNIQUE HASH clause.

  • If your application performs only exact match lookups on the primary key, then a hash index may offer better response time and throughput. In such a case, specify the UNIQUE HASH clause. See "CREATE TABLE" for more information about the UNIQUE HASH clause.

• Use ALTER TABLE to change the representation of the primary key index or resize a hash index.

• You cannot add or drop columns in the materialized view with the ALTER TABLE statement. To change the structure of the materialized view, drop and re-create the view.

• Use the DROP [MATERIALIZED] VIEW statement to drop a materialized view.

There are several restrictions on the query that is used to define the materialized view:

• A SELECT * query in a materialized view definition is expanded when the view is created. Columns added to the detail table after a materialized view is created do not affect the materialized view.

• Temporary tables cannot be used in a materialized view definition. Non-materialized views and derived tables cannot be used to define a materialized view.

• All columns in the GROUP BY list must be included in the select list.

• Aggregate view must include a COUNT(*) in the select list.

• SUM and COUNT are allowed, but not expressions involving them, including AVG.

• The following cannot be used in a SELECT statement that is creating a materialized view:

  • DISTINCT

  • FIRST

  • HAVING

  • ORDER BY

  • UNION

  • UNION ALL

  • MINUS

  • INTERSECT

  • JOIN

  • User functions: USER, CURRENT_USER, SESSION_USER

  • Subqueries

  • NEXTVAL and CURRVAL

  • Derived tables and joined tables

• Each expression in the select list must have a unique name. The name of a simple column expression is that column's name unless a column alias is defined. ROWID is considered an expression and needs an alias.

• No SELECT FOR UPDATE or SELECT FOR INSERT statements can be used on a view.

• Each inner table can only be outer joined with at most one table.

• Self joins are allowed. A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.

There are no additional restrictions on asynchronous materialized views with full (COMPLETE) refresh.

In addition to the restrictions in a SELECT statement that is creating a materialized view, the following restrictions apply when creating asynchronous materialized views with incremental (FAST) refresh:

• Aggregate functions are not supported

• Outer joins are not supported.

• The SELECT list must include the ROWID or the primary key columns for all the detail tables.

• The materialized view log must be created for each detail table in the asynchronous material view with incremental refresh before creating the asynchronous materialized view.

• The materialized view log must include all the columns used in the asynchronous materialized views.

• TimesTen creates a unique index for a asynchronous materialized views that are refreshed incrementally. The index is created on the primary key or ROWID of the detail tables included in the SELECT list.

Examples

Create a materialized view of columns from the customer and bookorder tables.

CREATE MATERIALIZED VIEW custorder AS
  SELECT custno, custname, ordno, book
    FROM customer, bookorder
    WHERE customer.custno=bookorder.custno;

Create a materialized view of columns x1 and y1 from the t1 table.

CREATE MATERIALIZED VIEW v1 AS SELECT x1, y1 FROM t1
  PRIMARY KEY (x1) UNIQUE HASH (x1) PAGES=100;

Create a materialized view from an outer join of columns x1 and y1 from the t1 and t2 tables.

CREATE MATERIALIZED VIEW v2 AS SELECT x1, y1 FROM t1, t2
  WHERE x1=x2(+);

Create an asynchronous materialized view called mv1 with incremental refresh. The materialized view will be refreshed immediately after updates to employees have been committed. The columns in mv1 are employee_id and email. (You must create a materialized view log before you create an asynchronous materialized view.)

CREATE MATERALIZED VIEW mv1 
  REFRESH FAST NEXT SYSDATE 
  AS SELECT employee_id, email FROM employees;
107 rows materialized.

See also

CREATE MATERIALIZED VIEW LOG
CREATE TABLE
CREATE VIEW
DROP [MATERIALIZED] VIEW
REFRESH MATERIALIZED VIEW