Returns information about the specified materialized views.

Syntax

sa_materialized_view_info( 
[ view_name 
[, owner_name ] ] 
)

Arguments

Remarks

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:

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.

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 unitialized 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).

Permissions

DBA authority, or execute permissions on DBO owned procedures.

Side effects

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

See also

• Improving performance with materialized views
• Change a manual view to an immediate view
• Additional restrictions for immediate views
• sa_materialized_view_can_be_immediate system procedure

Example

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 );

From the results you can see that: