Creating a Materialized View

To create a materialized view, use the SQL statement CREATE MATERIALIZED VIEW.

Note:

In order to create a materialized view, the user must have the appropriate privileges, which are described along with the syntax for all SQL statements in the SQL Statements chapter in the Oracle TimesTen In-Memory Database SQL Reference.

If the owner has these privileges revoked for any of the detail tables on which the materialized view is created, the materialized view becomes invalid. See Object Privileges for Materialized Views in the Oracle TimesTen In-Memory Database Security Guide.

When creating a materialized view, you can establish primary keys and the size of the hash table in the same manner as described for tables in Primary Keys, Foreign Keys, and Unique Indexes.

The SELECT query used to define the contents of a materialized view is similar to the top-level SQL SELECT statement described in SQL Statements in the Oracle TimesTen In-Memory Database SQL Reference with some restrictions, which are described in CREATE MATERIALIZED VIEW in the Oracle TimesTen In-Memory Database SQL Reference.

You can create a synchronous materialized view with the CREATE MATERIALIZED VIEW statement. A synchronous materialized view is automatically updated each time the detail tables are updated.

The following example creates a synchronous materialized view, named sample_mv, that generates a result set from selected columns in the customer and book_order detail tables described above.

Command> CREATE TABLE customer 
(cust_id INT NOT NULL, 
cus_name CHAR(100) NOT NULL, 
addr CHAR(100), 
zip INT, 
region CHAR(10), 
PRIMARY KEY (cust_id));

CREATE TABLE book_order 
(order_id INT NOT NULL, 
cust_id INT NOT NULL, 
book CHAR(100), 
PRIMARY KEY (order_id), 
FOREIGN KEY (cust_id) REFERENCES customer(cust_id));

Command> CREATE MATERIALIZED VIEW sample_mv AS 
SELECT customer.cust_id, cust_name, order_id, book 
FROM customer, book_order 
WHERE customer.cust_id=book_order.cust_id;