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.