All Products
Search
Document Center

AnalyticDB:Manage materialized views

Last Updated:Apr 18, 2024

This topic describes how to manage materialized views.

View the definition of a materialized view

  • Required permissions

    • You must have the SELECT permission on materialized views.

  • Syntax

    • SHOW CREATE MATERIALIZED VIEW <mv_name>
  • Description

    • Returns details about a DDL statement that is used to create a materialized view.

Query materialized views

  • Required permissions

    • You must have SELECT permissions on materialized views.

  • Syntax

    • SHOW MATERIALIZED VIEWS [LIKE 'pattern']
  • Description

    • Returns a list of materialized views on which you have the SELECT permission. If you specify a pattern, all materialized views that match the pattern are returned.

Delete a materialized view

  • Required permissions

    • You must have the DROP permission on materialized views.

  • Syntax

    • DROP MATERIALIZED VIEW <mv_name>
  • Description

    • Deletes a materialized view. You cannot execute the DROP TABLE statement to delete materialized views.

Materialized view information tables

  • information_schema.mv_info

    • This table contains the metadata of all materialized views.

    • The following table describes the common fields.

      Field

      Description

      MV_SCHEMA

      The name of the database where the materialized view is stored.

      MV_NAME

      The name of the materialized view.

      MV_DEFINITION

      The DDL statements that define the materialized view.

      MV_QUERY

      The SQL statements that you can execute to query the materialized view.

      FIRST_REFRESH_TIME

      The time of the first refresh if auto-refresh is configured.

      NEXT_REFRESH_TIME_FUNC

      The function that deduces the next refresh time if auto-refresh is configured.

      OWNER

      The creator of the materialized view.

    • If you want to view the definition of a single materialized view, we recommend that you execute the SHOW CREATE MATERIALIZED VIEW statement.

    • The following example shows how to query the information_schema.mv_info table:

      # Query the total number of materialized views in the cluster.
      SELECT count(*) FROM information_schema.mv_info
  • information_schema.mv_auto_refresh_jobs

    • This table contains the auto-refresh records of materialized views. Only the materialized views for which auto-refresh is configured are displayed in this table. Up to 1,024 materialized views can be displayed.

    • The following table describes the common fields.

      Field

      Description

      MV_SCHEMA

      The name of the database where the materialized view is stored.

      MV_NAME

      The name of the materialized view.

      SCHEDULED_START_TIME

      The scheduled start time of the refresh.

      START_TIME

      The start time of the refresh.

      END_TIME

      The end time of the refresh.

      STATE

      The status of the refresh. Valid values:

      • PENDING: The refresh is to be triggered.

      • RUNNING: The refresh is in progress. If multiple materialized views are refreshed at the same time, the views may be waiting for computing resources.

      • FINISHED: The refresh is complete.

      • FAILED: The refresh failed. If a refresh fails, it is retried until the allowed number of retries is reached. If the refresh still fails, the refresh task is canceled.

      • TIMEOUT: The refresh times out and is in an unknown state.

    • The following example shows how to query the information_schema.mv_auto_refresh_jobs table:

      # Query the auto-refresh records of the mymv view.
      SELECT * FROM information_schema.mv_auto_refresh_jobs where mv_name = 'mymv'

Modify a materialized view

  • Required permissions

    • You must have the ALTER permission on materialized views.

  • Syntax

    • ALTER MATERIALIZED VIEW <mv_name>
      [<ENABLE|DISABLE> QUERY REWRITE]
  • Description

    • <ENABLE|DISABLE> QUERY REWRITE: enables or disables the query rewrite feature for a materialized view.