Working with 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). Consequently, many operations that you perform on tables can be performed on materialized views as well. For example, you can build indexes on, and unload from, materialized views.

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; no data-altering operations such as INSERT, LOAD, DELETE, and UPDATE can be used on them.

Column statistics are generated and maintained for materialized views in exactly the same manner as for tables. See Optimizer estimates and column statistics.

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

Types of materialized views (manual and immediate)

There are two types of materialized views: manual and immediate, which implies the refresh type for the materialized view.

Manual views A manual materialized view, or manual view, is a materialized view with a refresh type defined as MANUAL REFRESH. Data in manual views can become stale because manual views are not refreshed until a refresh is explicitly requested, for example by using the REFRESH MATERIALIZED VIEW statement or the sa_refresh_materialized_views system procedure. By default, when you create a materialized view, it is a manual view.
A manual view is considered stale as soon as any of the underlying tables change, even if the change does not impact data in the materialized view. You can determine whether a manual view is considered stale by examining the DataStatus value returned by the sa_materialized_view_info system procedure. If S is returned, the manual view is stale.
Immediate views An immediate materialized view, or immediate view, is a materialized view with a refresh type defined as IMMEDIATE REFRESH. Data in an immediate view is automatically refreshed when changes to the underlying tables affect data in the view. If changes to the underlying tables do not impact data in the view, the view is not refreshed.
Also, when an immediate view is refreshed, only the rows that need to be changed are acted upon. This is different from refreshing a manual view, where all data is dropped and recreated for a refresh.

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. See Changing the refresh type for a materialized view.

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. See Materialized view statuses and properties.

Retrieving materialized view information from the database

Status and property information You can request information, such as the status of a materialized view, using the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.
See also Materialized view statuses and properties.

Database option information You can retrieve the database options that were stored with a materialized view when it was created by querying the SYSMVOPTION system view. The following statements create a materialized view and then query the database to find out the database options used when creating the view.

CREATE MATERIALIZED VIEW EmployeeConfid15 AS SELECT EmployeeID, Employees.DepartmentID, SocialSecurityNumber, Salary, ManagerID, Departments.DepartmentName, Departments.DepartmentHeadID FROM Employees, Departments WHERE Employees.DepartmentID=Departments.DepartmentID; SELECT option_name, option_value FROM SYSMVOPTION JOIN SYSMVOPTIONNAME WHERE SYSMVOPTION.view_object_id=( SELECT object_id FROM SYSTAB WHERE table_name='EmployeeConfid15' ) ORDER BY option_name;

Caution

When you are done with this example, you should drop the materialized view you created. Otherwise, you will not be able to make schema changes to its underlying tables Employees and Departments, when trying out other examples. You cannot alter the schema of a table that has enabled, dependent materialized view. See Dropping materialized views.

Dependency information To determine the list of views dependent on a materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.
This information can also be found in the SYSDEPENDENCY system view. See SYSDEPENDENCY system view.