sa_materialized_view_info system procedure

sa_materialized_view_info( 
[ view_name 
[, owner_name ] ] 
)

view_name Use this optional CHAR(128) parameter to specify the name of the materialized view for which to return information.
owner_name Use this optional CHAR(128) parameter to specify the owner of the materialized view.

If neither view_name nor owner_name are specified, information about all materialized views in the database is returned.

If owner_name is not specified, information about all materialized views named view_name is returned.

The sa_materialized_view_info system procedure returns the following information for a materialized view:

Column name	Data type	Description
OwnerName	CHAR(128)	The owner of the view.
ViewName	CHAR(128)	The name of the view.
Status	CHAR(1)	Status information about the view. Possible values are: D disabled E enabled
DataStatus	CHAR(1)	Status information about data in the view. Possible values are: E An error occurred during the last refresh attempt. The view is enabled, but uninitialized. F The underlying tables have not changed since the last refresh, and the view is considered fresh. The view is enabled and initialized. N The view is uninitialized. This occurs when one of the following is true: the view has not been refreshed since it was created the data has been truncated from the view the view is disabled S An underlying table has changed since the last refresh, and the view is considered stale. The view is enabled and initialized.
ViewLastRefreshed	TIMESTAMP	The time when the view was last refreshed. If the value of ViewLastRefreshed is NULL, the view is uninitialized.
DataLastModified	TIMESTAMP	For a stale view, the last time that underlying data was modified. The value is NULL for views that are not initialized, or for views that are not considered stale.
AvailForOptimization	CHAR(1)	Information about the availability of the view for use by the optimizer. Possible values are: D Use by the optimizer is disabled. The owner of the view doesn't allow the view to be used by the optimizer. I The view cannot be used by the optimizer for some internal reason, for example its definition doesn't meet the conditions required. However, the owner has not explicitly disallowed its use by the optimizer. N The view contains no data because a refresh has not been done or has failed. The view can be used by the optimizer by the owner of the view, but it is not initialized. O There is an incompatible option value for current connection. The view can be used by the optimizer and its definition meets all the required conditions, but the current option settings are not compatible with the options settings used to create the view. Y The view can be used by the optimizer. The owner of the view allows the view to be used by the optimizer and the view definition meets all the conditions needed to be used by the optimizer.
RefreshType	CHAR(1)	The refresh type for the view. Possible values are: I The view is an immediate view. Immediate views are refreshed immediately when changes to the data in an underlying table impact the data in the materialized view. M The view is a manual view. Manual views are refreshed manually, for example using the REFRESH MATERIALIZED VIEW statement, or the sa_refresh_materialized_views system procedure.

This procedure can be useful for determining the list of materialized views that will never be considered by the optimizer because of a problem with the view definition. The AvailForOptimization value is I for these materialized views.

The following table shows how the AvailForOptimization property is determined. Starting from the left column, you read across the row to see the conditions that must be in place to result in the value found in the AvailForOptimization column.

User allows view to be used in optimization?	The view definition satisfies all the conditions required for use?	The connection options match those required for use of the view?	The view is initialized?	AvailForOptimization value
Yes	Yes	Yes	Yes	Y
No	N/A	N/A	N/A	D
Yes	No	N/A	Yes	I
Yes	N/A	N/A	No	N
Yes	Yes	No	Yes	O

An initialized materialized view can be empty. This occurs when there is no data in the underlying tables that meets the materialized view definition. An empty view is not considered the same as an uninitialized materialized view, which also has no data in it. The value of the ViewLastRefreshed property allows you to distinguish between whether the view is uninitialized (NULL), or empty because of data in the underlying tables (non-NULL).

DBA authority, or execute permissions on DBO owned procedures.

All metadata for the specified materialized views, and all dependencies, are loaded into the database server cache.

The following statement returns information about all materialized views in the database:

SELECT *
   FROM sa_materialized_view_info();

The results of the sa_materialized_view_info system procedure can be combined with the results of the sa_materialized_view_can_be_immediate system procedure to return status information, and whether the view is eligible for being an immediate view. Execute the following statements to create materialized views that are examined for this example:

CREATE MATERIALIZED VIEW view0 AS ( 
   SELECT ID, Name, Description, Size 
   FROM Products 
   WHERE Quantity > 0 );
CREATE UNIQUE INDEX u_view0 
   ON view0( ID );
ALTER MATERIALIZED VIEW view0 
   IMMEDIATE REFRESH;
CREATE MATERIALIZED VIEW view00 AS (
   SELECT ID, Name, Description, Size 
   FROM Products 
   WHERE Quantity <= 0 );
CREATE UNIQUE INDEX u_view00 
   ON view00( ID );
CREATE MATERIALIZED VIEW view1 AS (
   SELECT ID, Name, Description, Size 
   FROM Products 
   WHERE Quantity = 0 );
ALTER MATERIALIZED VIEW view1 
   DISABLE;
CREATE MATERIALIZED VIEW view100
   AS (SELECT C.ID, C.Surname, sum(P.UnitPrice) as revenue, C.CompanyName, SO.OrderDate
         FROM Customers C, SalesOrders SO, SalesOrderItems SOI, Products P
         WHERE C.ID = SO.CustomerID
         AND SO.ID = SOI.ID
         AND P.ID = SOI.ProductID
         GROUP BY C.ID, C.Surname, C.CompanyName, SO.OrderDate);
REFRESH MATERIALIZED VIEW view100;

Execute the following statement to return the status and eligibility information for the views you just created:

SELECT ViewName, Status, ViewLastRefreshed, AvailForOptimization, RefreshType, CanBeImmediate 
FROM sa_materialized_view_info() AS V, 
   LATERAL( SELECT LIST( ErrorMessage, '' ) 
            FROM sa_materialized_view_can_be_immediate( V.ViewName, V.OwnerName ) ) AS I( CanBeImmediate );;

ViewName	Status	ViewLastRefreshed	AvailForOptimization	RefreshType	CanBeImmediate
view0	E	(NULL)	N	I
view00	E	(NULL)	N	M
view1	D	(NULL)	N	M	`Cannot use view 'view1' because it has been disabled`
view100	E	2008-02-12 16:47:00.000	Y	M	`The materialized view view100 cannot be changed to immediate because it has already been initialized. The materialized view view100 cannot be changed to immediate because it does not have a unique index on non-nullable columns. The materialized view cannot be changed to immediate because COUNT(*) is required to be part of the SELECT list. The materialized view cannot be changed to immediate because it does not have a unique index on non-aggregate non-nullable columns`.

From the results you can see that:

view0 was never refreshed and is an immediate view.
view00 was never refreshed and is a manual view.
view1 is disabled
view100 is a manual view that was last refreshed at 2008-02-12 16:47:00.000.
view00 can be changed to an immediate view because there are no error messages in the CanBeImmediate column.
view1 and view100 cannot be changed to immediate views for the reasons listed in the CanBeImmediate column.

sa_materialized_view_info system procedure

Syntax

Arguments

Remarks

Permissions

Side effects

See also

Example