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:

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

  2. Ensure that the view selects all of those key columns. In this example, the materialized view should select K1, K2, and K3.

  3. 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:

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

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

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