Returns information about the specified materialized views.
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. The default is NULL.
owner_name Use this optional CHAR(128) parameter to specify the owner of the materialized view. The default is NULL.
|Column name||Data type||Description|
|OwnerName||CHAR(128)||The owner of the view.|
|ViewName||CHAR(128)||The name of the view.|
Status information about the view. Possible values are:
Status information about data in the view. Possible values are:
The local time when the view was last refreshed. If the value of ViewLastRefreshed is NULL, the view is uninitialized.
For a stale view, the last local time that underlying data was modified.
The value is NULL for views that are not initialized, or for views that are not considered stale.
Information about the availability of the view for use by the optimizer. Possible values are:
The refresh type for the view. Possible values are:
If neither view_name nor owner_name are specified or both are NULL, information about all materialized views in the database is returned.
If owner_name is not specified or is NULL, information about all materialized views named view_name is returned.
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|
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).
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 owned by you:
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 ) WHERE OwnerName = USER_NAME();
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.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|