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 is refreshed synchronously with regard to changes in the detail tables.

Required Privileges

User executing the statement must have CREATE MATERIALIZED VIEW (if owner) or CREATE ANY MATERIALIZED VIEW (if not owner) privilege.

Owner of the materialized view must have:
  • SELECT privilege on the detail tables.
  • CREATE TABLE privilege.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. You must specify the DISTRIBUTE BY HASH clause and you must define a distribution key. The DISTRIBUTE BY REFERENCE and DUPLICATE clauses are not supported.

SQL Syntax: TimesTen Scaleout

CREATE MATERIALIZED VIEW [Owner.]ViewName
DISTRIBUTE BY HASH (ColumnName [,...])
AS SelectQuery
[PRIMARY KEY (ColumnName [,...])] 
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]

SQL Syntax: TimesTen Classic

CREATE MATERIALIZED VIEW [Owner.]ViewName
AS SelectQuery
[PRIMARY KEY (ColumnName [,...])] 
[UNIQUE HASH ON (HashColumnName [,...]) PAGES = PrimaryPages]

Parameters

Parameter Description

[Owner.]ViewName

Name assigned to the new view.

DISTRIBUTE BY HASH (ColumnName [,...])

TimesTen Scaleout only. You must specify the DISTRIBUTE BY HASH clause and you must specify one or more columns for the distribution key (even if you have specified a primary key).

The detail table must be distributed by hash.

DISTRIBUTE BY REFERENCE or DUPLICATE clauses are not supported.

This clause must appear before the AS SelectQuery clause.

SelectQuery

Select column from the detail tables to be used in the view.

ColumnName

Name of the column(s) that forms the primary key for the view to be created. Up to 32 columns can be specified for the primary key. Each result column name of a viewed table must be unique. The column name definition cannot contain the table or owner component.

UNIQUE HASH ON

Hash index for the table. Only unique hash indexes are created. This parameter is used for equality predicates. UNIQUE HASH ON requires that a primary key be defined.

HashColumnName

Column defined in the view that is to participate in the hash key of this table. The columns specified in the hash index must be identical to the columns in the primary key.

PAGES = PrimaryPages

Sizes the hash index to reflect the expected number of pages in your table. To determine the value for PrimaryPages, divide the number of expected rows in your table by 256. For example, if your table has 256,000 rows, specify 1000 for PrimaryPages (256000/256=1000).

The value for PrimaryPages must be a positive constant and must be greater than 0.

If your estimate for PrimaryPages is too small, performance may be degraded.

See CREATE TABLE for information on hash indexes.

Description and Restrictions for CREATE MATERIALIZED VIEW: TimesTen Scaleout

Description and restrictions include:

  • The SQL optimizer may re-write a query against a base table to use an available materialized view if the use of the materialized view is expected to improve the execution time of the query.

  • You must specify the DISTRIBUTE BY HASH clause and you must specify it with a distribution key (even if you have specified a primary key and intend to use the primary key as the distribution key).

  • You must specify the DISTRIBUTE BY HASH clause before the AS SelectQuery clause.

  • You can only specify the DISTRIBUTE BY HASH clause. The DISTRIBUTE BY REFERENCE and DUPLICATE clauses are not supported.

  • The SelectQuery must be restricted to single table SELECT statements.

  • You cannot specify the GROUP BY or the WHERE clause in the SelectQuery.

  • You cannot use SQL functions in the SelectQuery.

  • You cannot use an expression in the SelectQuery.

  • The detail table of the materialized view cannot have a foreign key with a cascade delete clause.

  • The distribution key columns must be in the project list of the SelectQuery.

  • There are no DDL rewrites. For example, if you create a unique index on the detail table, a corresponding index on the materialized view (which is distributed on the unique column) is not created.

Description: TimesTen Scaleout and TimesTen Classic

The restrictions and requirements on the defining query include:

  • Each expression in the select list must have a unique name.

  • Do not use non-materialized views to define a materialized view.

  • Do not define CLOB, BLOB, or NCLOB data types for columns in the select list of the materialized view query.

  • The detail tables cannot belong to a cache group and the detail tables cannot have compression.

  • Do not use SELECT FOR UPDATE.

  • Do not reference system tables or views.

  • Do not use nested definitions for a materialized view.

  • Do not use dynamic parameters.

  • Do not use ROWNUM.

  • Do not use analytic functions.

  • Do not use GROUPING SETS, ROLLUP, or CUBE.

  • Do not use the SYSDATE function.

  • Do not use the functions SYSTEM_USER, USER, CURRENT_USER, or SESSION_USER.

  • Do not use NEXTVAL or CURRVAL.

  • Outer joins are allowed but the select list must project at least one non-nullable column from each of the inner tables specified in the outer join.

  • Do not use the WITH subquery clause.

The restrictions (not on the defining query) include:

  • Do not have a hash-based primary key that contains any aggregate columns of the materialized view.

  • A materialized view cannot be replicated directly using TimesTen replication. You can replicate the detail tables. You must define the same materialized view on both sides of replication. TimesTen automatically updates the corresponding materialized views.

  • You cannot define a foreign key if the referencing or referenced table is a materialized view.

The following restrictions and requirements on the defining query are:

  • The view definition must include all columns in the group by list in the select list.

  • An aggregate view must include a COUNT (*) or COUNT (non-nullable column) in the select list.

  • Do not use derived tables or JOIN tables.

  • Do not use SELECT DISTINCT or an aggregate distinct function.

  • Do not use the set operators UNION, MINUS, or INTERSECT.

  • Do not use SUM of nullable expressions.

  • Use only simple columns as group by columns.

  • Group by columns cannot belong to self join tables.

  • Do not use these clauses:

    • HAVING

    • ORDER BY

    • DISTINCT

    • FIRST

    • JOIN

  • Do not use the TT_HASH function.

  • You can use SUM and COUNT but do not use expressions involving SUM and COUNT. Do not use AVG, which is treated as SUM/COUNT.

  • Do not specify MIN or MAX functions in the select list.

  • For joins:

    • Join predicates cannot have an OR.

    • Do not specify Cartesian product joins (joins with no join predicate).

    • For outer joins, outer join each inner table with at most one table.

Additional considerations include:

  • 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.

  • By default, a range index is created to enforce the primary key for a materialized view. Alternatively, 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.

  • You can use ALTER TABLE to change the representation of the primary key index or resize a hash index of a materialized view.

  • 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 recreate the view.

  • You can create indexes on the materialized view with the CREATE INDEX SQL statement.

The owner of a materialized view must have the SELECT privilege on its detail tables. The SELECT privilege is implied by the SELECT ANY TABLE and ADMIN system privileges. When the SELECT privilege or a higher-level system privilege on the detail tables is revoked from the owner of the materialized view, the materialized view becomes invalid.

Selecting from an invalid materialized view fails with an error. Updates to the detail tables of an invalid materialized view do not update the materialized view.

You can identify invalid materialized views by using the ttIsql describe command and by inspecting the STATUS column of the SYS.DBA_OBJECTS, SYS.ALL_OBJECTS or SYS.USER_OBJECTS system tables. See Oracle TimesTen In-Memory Database System Tables and Views Reference.

If the revoked privilege is restored, you can make an invalid materialized view valid again by dropping and recreating the materialized view.

For more information, see Object Privileges for Materialized Views in Oracle TimesTen In-Memory Database Security Guide.

Examples for CREATE MATERIALIZED VIEW: TimesTen Scaleout

Syntax example:

Command> CREATE MATERIALIZED VIEW mv
             DISTRIBUTE BY HASH (phone)
             AS SELECT phone FROM accounts;
1010 rows materialized.

Examples: TimesTen Classic

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 ON (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(+);

The following example creates a materialized view empmatview2 based on selected columns employee_id and email from table employees. After the materialized view is created, create an index on the materialized view column mvemp_id of the materialized view empmatview2. 

CREATE MATERIALIZED VIEW empmatview2
   AS SELECT employee_id mvemp_id, email mvemail 
         FROM employees;
107 rows materialized. 

CREATE INDEX empmvindex ON empmatview2 (mvemp_id);

See Also