Note
This view was introduced in MySQL 4.3.0.
Description
The ALL_MVIEWS view displays information about materialized views.
Field descriptions
Field name |
Type |
NULLable |
Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | YES | The name of the owner of the materialized view. |
| MVIEW_NAME | VARCHAR2(128) | YES | The name of the materialized view. |
| CONTAINER_NAME | VARCHAR2(128) | YES | The name of the container table of the materialized view. |
| QUERY | CLOB | NO | The query that defines the materialized view. |
| QUERY_LEN | NUMBER | NO | The length of the query, in bytes. |
| UPDATABLE | VARCHAR2(1) | NO | Indicates whether the materialized view is updatable.
NoteThis field is compatible and its default value is N. |
| UPDATE_LOG | VARCHAR2(128) | NO | The name of the file that records update logs for updatable materialized views.
NoteThis field is compatible and its default value is NULL. |
| MASTER_ROLLBACK_SEG | VARCHAR2(128) | NO | The rollback segment of the primary site or primary materialized view site.
NoteThis field is compatible and its default value is NULL. |
| MASTER_LINK | VARCHAR2(128) | NO | The database link of the primary site or primary materialized view site.
NoteThis field is compatible and its default value is NULL. |
| REWRITE_ENABLED | VARCHAR2(1) | NO | Indicates whether query rewriting is enabled for the materialized view.
|
| REWRITE_CAPABILITY | VARCHAR2(9) | NO | Indicates whether the materialized view is eligible for query rewriting. If yes, the materialized view must follow the rules specified below:
NoteThis field is compatible and its default value is NULL. |
| REFRESH_MODE | VARCHAR2(6) | NO | The refresh mode of the materialized view.
|
| REFRESH_METHOD | VARCHAR2(8) | NO | The default refresh method used to refresh the materialized view (can be overwritten by an API).
|
| BUILD_MODE | VARCHAR2(9) | NO | The strategy for filling the materialized view during creation.
|
| FAST_REFRESHABLE | VARCHAR2(18) | NO | Indicates whether the materialized view is suitable for incremental (fast) refreshes.
NoteThis field is compatible and its default value is NULL. |
| LAST_REFRESH_TYPE | VARCHAR2(8) | NO | The method used in the last refresh.
|
| LAST_REFRESH_DATE | DATE | YES | The date when the materialized view was last refreshed. |
| LAST_REFRESH_END_TIME | DATE | NO | The end time of the last refresh of the materialized view. |
| STALENESS | VARCHAR2(19) | NO | The relationship between the content of the materialized view and that of its master.
NoteThis field is compatible and its default value is NULL. |
| AFTER_FAST_REFRESH | VARCHAR2(19) | NO | The value of the STALENESS field when a fast refresh is applied to the materialized view. It has the same possible values as the STALENESS field. If the value is NA, the STALENESS field will be set to this value when fast refreshes are not applicable to the materialized view.
NoteThis field is compatible and its default value is NULL. |
| UNKNOWN_PREBUILT | VARCHAR2(1) | NO | Indicates whether the materialized view is prebuilt.
NoteThis field is compatible and its default value is N. |
| UNKNOWN_PLSQL_FUNC | VARCHAR2(1) | NO | Indicates whether the materialized view contains a PL/SQL function.
NoteThis field is compatible and its default value is N. |
| UNKNOWN_EXTERNAL_TABLE | VARCHAR2(1) | NO | Indicates whether the materialized view contains an external table.
NoteThis field is compatible and its default value is N. |
| UNKNOWN_CONSIDER_FRESH | VARCHAR2(1) | NO | Indicates whether the materialized view is considered the latest.
NoteThis field is compatible and its default value is N. |
| UNKNOWN_IMPORT | VARCHAR2(1) | NO | Indicates whether the materialized view is imported.
NoteThis field is compatible and its default value is N. |
| UNKNOWN_TRUSTED_FD | VARCHAR2(1) | NO | Indicates whether the materialized view uses trusted constraints for refreshes.
NoteThis field is compatible and its default value is N. |
| COMPILE_STATE | VARCHAR2(19) | NO | The validity of the materialized view relative to the objects on which it depends.
NoteThis field is compatible and its default value is NULL. |
| USE_NO_INDEX | VARCHAR2(1) | NO | The materialized view was created using the USING NO INDEX clause (Y) or the default index (N). The USING NO INDEX clause prohibits the creation of a default index.
NoteThis field is compatible and its default value is Y. |
| STALE_SINCE | DATE | NO | The start time when the materialized view became stale.
NoteThis field is compatible and its default value is NULL. |
| NUM_PCT_TABLES | NUMBER | NO | The number of PCT base tables.
NoteThis field is compatible and its default value is NULL. |
| NUM_FRESH_PCT_REGIONS | NUMBER | NO | The number of latest PCT partition or subpartition regions.
NoteThis field is compatible and its default value is NULL. |
| NUM_STALE_PCT_REGIONS | NUMBER | NO | The number of stale PCT partition or subpartition regions.
NoteThis field is compatible and its default value is NULL. |
| SEGMENT_CREATED | VARCHAR2(3) | NO | Indicates whether the materialized view was created using the SEGMENT CREATION DEFERRED clause.
NoteThis field is compatible and its default value is NO. |
| EVALUATION_EDITION | VARCHAR2(128) | NO | The name of the version object referenced in the expression column of the materialized view.
NoteThis field is compatible and its default value is NULL. |
| UNUSABLE_BEFORE | VARCHAR2(128) | NO | The name of the oldest version where the storage results of the materialized view subquery can be used for query rewriting. The storage results of the materialized view in versions earlier than the specified version are considered unavailable. If such a version is not specified, the value of this field is NULL.
NoteThis field is compatible and its default value is NULL. |
| UNUSABLE_BEGINNING | VARCHAR2(128) | NO | The name of the oldest version where the storage results of the materialized view subquery cannot be used for query rewriting. In versions from the specified version to its descendants, the data is unavailable for query rewriting. If such a version is not specified, the value of this field is NULL.
NoteThis field is compatible and its default value is NULL. |
| DEFAULT_COLLATION | VARCHAR2(100) | NO | The default sorting rule of the materialized view.
NoteThis field is compatible and its default value is NULL. |
| ON_QUERY_COMPUTATION | VARCHAR2(1) | NO | Indicates whether the materialized view is a real-time materialized view.
|
Examples
obclient [SYS]> SELECT * FROM SYS.ALL_MVIEWS WHERE ROWNUM < = 1;
The query result is as follows:
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
| OWNER | MVIEW_NAME | CONTAINER_NAME | QUERY | QUERY_LEN | UPDATABLE | UPDATE_LOG | MASTER_ROLLBACK_SEG | MASTER_LINK | REWRITE_ENABLED | REWRITE_CAPABILITY | REFRESH_MODE | REFRESH_METHOD | BUILD_MODE | FAST_REFRESHABLE | LAST_REFRESH_TYPE | LAST_REFRESH_DATE | LAST_REFRESH_END_TIME | STALENESS | AFTER_FAST_REFRESH | UNKNOWN_PREBUILT | UNKNOWN_PLSQL_FUNC | UNKNOWN_EXTERNAL_TABLE | UNKNOWN_CONSIDER_FRESH | UNKNOWN_IMPORT | UNKNOWN_TRUSTED_FD | COMPILE_STATE | USE_NO_INDEX | STALE_SINCE | NUM_PCT_TABLES | NUM_FRESH_PCT_REGIONS | NUM_STALE_PCT_REGIONS | SEGMENT_CREATED | EVALUATION_EDITION | UNUSABLE_BEFORE | UNUSABLE_BEGINNING | DEFAULT_COLLATION | ON_QUERY_COMPUTATION |
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
| USER001 | MV_TEST_TBL1 | __mv_container_500154 | select "USER001"."TEST_TBL1"."COL1" AS "COL1","USER001"."TEST_TBL1"."COL2" AS "COL2","USER001"."TEST_TBL1"."COL3" AS "COL3" from "USER001"."TEST_TBL1" where ("USER001"."TEST_TBL1"."COL3" >= 30) | 193 | N | NULL | NULL | NULL | N | NULL | DEMAND | COMPLETE | IMMEDIATE | NULL | COMPLETE | 19-JAN-24 | 19-JAN-24 | NULL | NULL | N | N | N | N | N | N | NULL | Y | NULL | NULL | NULL | NULL | NO | NULL | NULL | NULL | NULL | N |
+---------+--------------+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+------------+---------------------+-------------+-----------------+--------------------+--------------+----------------+------------+------------------+-------------------+-------------------+-----------------------+-----------+--------------------+------------------+--------------------+------------------------+------------------------+----------------+--------------------+---------------+--------------+-------------+----------------+-----------------------+-----------------------+-----------------+--------------------+-----------------+--------------------+-------------------+----------------------+
1 row in set (0.047 sec)