Understanding Materialized Views

The following sections describes materialized views and how to manage them:

Overview of Materialized Views

A materialized view is a read-only table that maintains a summary of data selected from one or more regular TimesTen tables. The TimesTen tables queried to make up the result set for the materialized view are called detail tables.

Note:

Materialized views are not supported on cache tables.

Figure 8-1 shows a materialized view created from detail tables. An application updates the detail tables and can select data from the materialized view.

Figure 8-1 Materialized View

Description of "Figure 8-1 Materialized View"

The synchronous materialized view, updates the result set data from the detail tables at the time of the detail table transaction. Every time data is updated in the detail tables, the result set is updated. Thus, the materialized view is never out of sync with the detail tables. However, this can affect your performance. A single transaction, the user transaction, runs the updates for both the detail table and any materialized views.

Working With Materialized Views

This section includes the following topics:

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;

Dropping a Materialized View

To drop any materialized view, issue the DROP VIEW statement.

The following statement drops the sample_mv materialized view.

Command> DROP VIEW sample_mv;

See SQL Statements chapter in the Oracle TimesTen In-Memory Database SQL Reference.

Restrictions on Materialized Views and Detail Tables

A materialized view is a read-only table that cannot be updated directly. This means a materialized view cannot be updated by an INSERT, DELETE, or UPDATE statement, nor can it be updated by replication, XLA, or the cache agent.

For example, any attempt to update a row in a materialized view generates the following error:

805: Update view table directly has not been implemented

Readers familiar with other implementations of materialized views should note the following characteristics of TimesTen materialized views:

Detail tables can be replicated, but materialized views cannot.
Neither a materialized view nor its detail tables can be part of a cache group.
You cannot create a foreign key to reference a table or another materialized view. Regular tables cannot use a foreign key to refer to a materialized view.
To drop a materialized view must use the DROP VIEW statement.
You cannot alter a materialized view. You must use the DROP VIEW statement and then create a new materialized view with a CREATE MATERIALIZED VIEW statement.
Materialized views must be explicitly created by the application.
The TimesTen query optimizer does not rewrite queries on the detail tables to reference materialized views. Application queries must directly reference views, if they are to be used.
There are some restrictions to the SQL used to create materialized views. See CREATE MATERIALIZED VIEW in the Oracle TimesTen In-Memory Database SQL Reference.

Performance Implications of Materialized Views

The performance of UPDATE, INSERT, and DELETE operations may be impacted if the updated table is referenced in a materialized view.

The performance impact depends on many factors, such as the following:

Nature of the materialized view: How many detail tables, whether outer join or aggregation, are used.
Which indexes are present on the detail table and on the materialized view.
How many materialized view rows are affected by the change.

A materialized view is a persistent, up-to-date copy of a query result. To keep the materialized view up to date, TimesTen must perform "materialized view maintenance" when you change a materialized view's detail table. For example, if you have a materialized view named V that selects from tables T1, T2, and T3, then any time you insert into T1, or update T2, or delete from T3, TimesTen performs "materialized view maintenance."

Materialized view maintenance needs appropriate indexes just like regular database operations. If they are not there, materialized view maintenance performs poorly.

All update, insert, or delete statements on detail tables have execution plans, as described in The TimesTen Query Optimizer. For example, an update of a row in T1 initiates the first stage of the plan where it updates the materialized view V, followed by a second stage where it updates T1.

For fast materialized view maintenance, you should evaluate the plans for all the operations that update the detail tables, as follows:

Examine all the WHERE clauses for the update or delete statements that frequently occur on the detail tables. Note any clause that uses an index key. For example, if the operations that an application performs 95 percent of the time are as follows:
```
UPDATE T1 set A=A+1 WHERE K1=? AND K2=?
DELETE FROMT2 WHERE K3=?
```
Then the keys to note are (K1, K2) and K3.
Ensure that the view selects all of those key columns. In this example, the materialized view should select K1, K2, and K3.
Create an index on the materialized view on each of those keys. In this example, the view should have two indexes, one on (V.K1, V.K2) and one on V.K3. The indexes do not have to be unique. The names of the view columns can be different from the names of the table columns, though they are the same in this example.

With this method, when you update a detail table, your WHERE clause is used to do the corresponding update of the view. This allows maintenance to run in a batch, which has better performance.

The above method may not always work, however. For example, an application may have many different methods to update the detail tables. The application would have to select far too many items in the view or create too many indexes on the materialized view, taking up more space or more performance than you might wish. An alternative method is as follows:

For each table in the materialized view's FROM clause (each detail table), check which ones are frequently changed by UPDATE, INSERT and CREATE VIEW statements. For example, a materialized view's FROM clause may have tables T1, T2, T3, T4, and T5, but of those, only T2 and T3 are frequently changed.
For each of those tables, make sure the materialized view selects its rowids. In this example, the materialized view should select T2.rowid and T3.rowid.
Create an index on the materialized view on each of those rowid columns. In this example, the columns might be called T2rowid and T3rowid, and indexes would be created on V.T2rowid and V.T3rowid.

With this method, materialized view maintenance is done on a row-by-row basis, rather than on a batch basis. But the rows can be matched very efficiently between a materialized view and its detail tables, which speeds up the maintenance. It is generally not as fast as the first method, but it is still good.