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.
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 information about the view. Possible values are:
Status information about data in the view. Possible values are:
The time when the view was last refreshed. If the value of ViewLastRefreshed is NULL, the view is uninitialized.
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.
Information about the availability of the view for use by the optimizer. Possible values are:
For more information about how, and whether, a materialized view is selected by the optimizer, see Improving performance with materialized views.
The refresh type for the view. Possible values are:
For more information about manual and immediate views, see Manual and immediate materialized views.
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. To learn more about the restrictions for materialized view definitions, see Restrictions on 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).
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 on 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 );
|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 view10 cannot be changed to immediate because it has already been initialized. The materialized view view10 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.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|