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.
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
[,...]) ASSelectQuery
[PRIMARY KEY (ColumnName
[,...])] [UNIQUE HASH ON (HashColumnName
[,...]) PAGES =PrimaryPages
]
SQL Syntax: TimesTen Classic
CREATE MATERIALIZED VIEW [Owner.]ViewName
ASSelectQuery
[PRIMARY KEY (ColumnName
[,...])] [UNIQUE HASH ON (HashColumnName [,...]) PAGES =PrimaryPages
]
Parameters
Parameter | Description |
---|---|
|
Name assigned to the new view. |
|
TimesTen Scaleout only. You must specify the The detail table must be distributed by hash.
This clause must appear before the |
|
Select column from the detail tables to be used in the view. |
|
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. |
|
Hash index for the table. Only unique hash indexes are created. This parameter is used for equality predicates. |
|
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. |
|
Sizes the hash index to reflect the expected number of pages in your table. To determine the value for The value for If your estimate for 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 theAS
SelectQuery
clause. -
You can only specify the
DISTRIBUTE
BY
HASH
clause. TheDISTRIBUTE
BY
REFERENCE
andDUPLICATE
clauses are not supported. -
The
SelectQuery
must be restricted to single tableSELECT
statements. -
You cannot specify the
GROUP
BY
or theWHERE
clause in theSelectQuery
. -
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
, orNCLOB
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
, orCUBE
. -
Do not use the
SYSDATE
function. -
Do not use the functions
SYSTEM_USER
,USER
,CURRENT_USER
, orSESSION_USER
. -
Do not use
NEXTVAL
orCURRVAL
. -
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 (*)
orCOUNT
(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
, orINTERSECT
. -
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
andCOUNT
but do not use expressions involvingSUM
andCOUNT
. Do not useAVG
, which is treated asSUM/COUNT
. -
Do not specify
MIN
orMAX
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
orINSERT
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 theUNIQUE 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);