Alter materialized views

You can use SQL to alter the materialize property of an SPM view, which can be ACTIVE, or SUSPEND. You can use REFRESH to change from the SUSPEND to the ACTIVE state.

• Using the SUSPEND option marks a materialized view and its associated table as not eligible for use in queries or transactions. The system truncates the materialized table and redirects all queries against the materialized view to the base table.

  Use SUSPEND to temporarily defer updates to materialized tables, such as when you are running reclaims, restores, or loads.

• Using the REFRESH option rematerializes the SPM view, which re-creates the materialized table from the base table. Although normally you use the REFRESH option on suspended materialized views, you can also use it on ordered unsuspended materialized views to sort the views again for better performance. You would also use the REFRESH option to update the materialized views after an insert to the base table.

To change the properties of an SPM view, enter:

   MYDB.SCHEMA(USER)=> ALTER VIEW customers_mview MATERIALIZE REFRESH;