The SVV_MV_INFO table contains a row for every materialized view, whether the data is stale, and state information.
For more information about materialized views, see Materialized views in Amazon Redshift.
SVV_MV_INFO is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.
Table columns
| Column name | Data type | Description |
|---|---|---|
| database_name | char(128) | The database that contains the materialized view. |
| schema_name | char(128) | The schema of the database. |
| user_name | char(128) | The user who owns the materialized view. |
| name | char(128) | The materialized view name. |
| is_stale | char(1) | A t indicates that the materialized
view is stale. A stale
materialized view is one where the base tables have been updated but
the materialized view hasn't been refreshed. This information
might not be accurate if a refresh hasn't been run since the
last restart. |
| state | integer | The state of the materialized view as follows:
|
| autorewrite | char(1) | A t indicates that the materialized
view is eligible for automatic rewriting of queries. |
| autorefresh | char(1) | A t indicates that the materialized
view can be automatically refreshed. |
Sample query
To view the state of all materialized views, run the following query.
select * from svv_mv_info;This query returns the following sample output.
database_name | schema_name | user_name | name | is_stale | state | autorefresh | autorewrite
--------------+-------------------------+-----------+---------+-----------+-------+-------------+----------------
dev | test_ivm_setup | catch-22 | mv | f | 1 | 1 | 0
dev | test_ivm_setup | lotr | old_mv | t | 1 | 0 | 1
