Materialized view statuses and properties

Materialized views are characterized by a combination of their status and properties. The status of a materialized view reflects the availability of the view for use by the database server. The properties of a materialized view reflect the state of the data within the view.

The best way to determine the status and properties of existing materialized views is to use the sa_materialized_view_info system procedure. See sa_materialized_view_info system procedure.

You can also view information about materialized views by choosing the Views folder in Sybase Central and examining the details provided for the individual views, or by querying the SYSTAB and SYSVIEW system views. See SYSTAB system view, and SYSVIEW system view.

Materialized view statuses

There are two possible statuses for materialized views:

Enabled A materialized view has the status enabled if it has been successfully compiled and it is available for use by the database server. An enabled materialized view may not have data in it. For example, if you truncate the data from an enabled materialized view, it changes to enabled and uninitialized. A materialized view can be initialized but empty if there is no data in the underlying tables that satisfies the definition for the materialized view. This is not the same as a materialized view that has no data in it because it is not initialized.
Disabled A materialized view has the status disabled only if you explicitly disable it, for example by using the ALTER MATERIALIZED VIEW ... DISABLE statement. When you disable a materialized view, the data and indexes for the view are dropped. Also, when you disable an immediate view, it is changed to a manual view.

To determine whether a view is enabled or disabled, use the sa_materialized_view_info system procedure to return the Status property for the view. See sa_materialized_view_info system procedure.

For information about enabling and disabling materialized views, see Enable and disable materialized views.

Materialized view properties

Materialized view properties are used by the optimizer when evaluating whether to use a view. The following list describes the properties for a materialized view that are returned by the sa_materialized_view_info system procedure:

Status The Status property indicates whether the view is enabled or disabled.
DataStatus The DataStatus property reflects the state of the data in the view. For example, it tells you whether the view is initialized and whether the view is stale. Manual views are stale if data in the underlying tables has changed since the last time the materialized view was refreshed. Immediate views are never stale.
ViewLastRefreshed The ViewLastRefreshed property indicates the last time the view was refreshed.
DateLastModified The DateLastModified property indicates the most recent time the data in any underlying table was modified if the view is stale.
AvailForOptimization The AvailForOptimization property reflects whether the view is available for use by the optimizer.
RefreshType The RefreshType property indicates whether it is a manual view or an immediate view.

For the list of possible values for each property, see sa_materialized_view_info system procedure.

While there is no property that tells you whether a manual view can be converted to an immediate view, you can determine this by using the sa_materialized_view_can_be_immediate system procedure. See sa_materialized_view_can_be_immediate system procedure.

Status and property changes when altering, refreshing, and truncating a materialized view

Operations you perform on a materialized view such as altering, refreshing, and truncating, impact the view's status and properties. The following diagram shows how these tasks impact the status and some of the properties of a materialized view.

In the diagram, each gray square is a materialized view; immediate views are identified by the term IMMEDIATE, and manual views by the term MANUAL. The term ALTER in the connectors between grey boxes is short for ALTER MATERIALIZED VIEW. Although SQL statements are shown for changing the materialized view status, you can also use Sybase Central to perform these activities.

Diagram showing the various states for materialized views

Some important concepts to note from the diagram are as follows:

When you create a materialized view, it is an enabled manual view and it is uninitialized (contains no data).
When you refresh an uninitialized view, it becomes initialized (populated with data).
Changing from a manual view to an immediate view requires several steps, and there are additional restrictions for immediate views. See Change a manual view to an immediate view, and Additional restrictions for immediate views.
When you disable a materialized view:
- the data is dropped
- the view reverts to uninitialized
- the indexes are dropped
- an immediate view reverts to manual