Materialized Views

A materialized view is a view whose result set has been computed and stored on disk, similar to a base table. Conceptually, a materialized view is both a view (it has a query specification stored in the catalog) and a table (it has persistent materialized rows). Materialized views are only supported for system store (IQ catalog store) tables.

Many operations that you perform on tables can be performed on materialized views as well. For example, you can build indexes on materialized views. Column statistics are generated and maintained for materialized views in exactly the same manner as for tables.

Consider using materialized views for frequently executed, expensive queries, such as those involving intensive aggregation and join operations. Materialized views provide a queryable structure in which to store aggregated, joined data. Materialized views are designed to improve performance in environments where the database is large, and where frequent queries result in repetitive aggregation and join operations on large amounts of data. For example, materialized views are ideal for use with data warehousing applications.

Materialized views are precomputed using data from the base tables that they refer to. Materialized views are read only; you cannot perform data-altering operations such as INSERT, LOAD, DELETE, and UPDATE on them.

While you can create indexes on materialized views, you cannot create keys, constraints, triggers, or articles on them.

You can change a manual view to an immediate view, and vice versa. However, the process for changing from a manual view to an immediate view has more steps. Changing the refresh type for a materialized view can impact the status and properties of the view, especially when you change a manual view to an immediate view.

• Creating a Materialized View
  Define a SQL query, using the Create Materialized View wizard, to create a new materialized view in the system store (IQ catalog store).
• Viewing Materialized View Data in the Execute SQL Window
  Execute a SELECT * FROM query and display contents of the materialized view in the query results pane of the Execute SQL window.
• Refreshing Materialized View Data
  Refresh materialized view data to apply any modifications to underlying base tables to the view.
• Truncating Materialized View Data
  Truncate and refresh the materialized view to increase performance. Truncation cleans up stale materialized view data without deleting the view definition, and without removing any rows from the underlying table.
• Validating Materialized View Data
  Validate the data in an enabled materialized view.
• Setting a Clustered Index
  Using a clustered index increases the chance that two rows from adjacent index entries will appear on the same page in the database. This can lead to performance benefits by reducing the number of times a table page needs to be read into the buffer pool.
• Recompiling and Enabling a Materialized View
  Recompile and enable a disabled materialized view to make it available for use by the database server.
• Disabling a Materialized View
  Disabling a materialized view makes it unusable, but retains the definition of the materialized view in the database. You can enable and recompile the disabled materialized view at a later time.
• Deleting a Materialized View
  Delete an enabled or disabled materialized view to remove it from the database. Deleting a materialized view deletes the definition of materialized view and its data, because a materialized view has its own physical storage (dbspace).
• Generating Materialized View DDL Commands
  Display the data description language SQL code for adding a materialized view to the database. The SQL code can be a useful reference and training tool.
• Viewing or Modifying Materialized View Properties
  View or change materialized view properties including the SQL view definition (including the column definition), the amount of free space you want to reserve for each materialized view page, permissions on the materialized view, and the refresh type.
• Materialized View Privilege Summary
  A list of the system privileges and object permissions required to complete the various materialized view tasks.
• Materialized View Permissions
  Grant or revoke materialized view permissions to users and roles.
• Materialized View Indexes
  You can add multiple indexes to any column in the materialized view to better support the queries you plan to run.