PolarDB:Fully refreshing a materialized view

The method for modifying PolarDB cluster parameters differs between the console and a database session. The differences are as follows:

• In the PolarDB console

  • Compatibility: Some cluster parameters in the PolarDB console have the loose_ prefix for compatibility with MySQL configuration files.

  • Procedure: Find and modify the parameters that have the loose_ prefix.

• In a database session (using the command line or a client)

  • Procedure: When you connect to the database and use the SET command to modify a parameter, remove the loose_ prefix and use the original parameter name.

Syntax

CREATE
  MATERIALIZED VIEW view_name [(column_list)]
  [REFRESH [COMPLETE|FAST]]
  [ON [COMMIT|DEMAND]]
  [START WITH now()] [NEXT now() + interval 1 hour]
  [[DISABLE|ENABLE] QUERY REWRITE]
  [MATERIALIZED OPTIONS = 'json_options']
  AS select_statement

Permissions

• The CREATE permission on the database where the materialized view is located.

• The SELECT permission on the relevant columns (or the entire table) of all base tables for the materialized view.

Parameters

mv_options usage

mv_options is specified by the MATERIALIZED OPTIONS = 'json_options' clause. The clause must appear before AS select_statement, and json_options should be a valid JSON object.

• Supported versions:

  • MySQL 8.0.1, kernel minor version 8.0.1.1.55 or later.

  • MySQL 8.0.2, kernel minor version 8.0.2.2.35 or later.

• mv_options supports the following fields:

  Field	Type	Value	Description
  order_key	STRING	A column name or expression text.	Sets the order_key of the container table of the materialized view.
  engine	STRING	InnoDB XEngine	Specifies the storage engine used by the container table of the materialized view. The value is case-insensitive.
  table_format	STRING	row column	Specifies the row-store or column-store format of the container table. The value is case-insensitive. The InnoDB engine supports only row. The XEngine engine supports both row and column, and is typically used together with engine: "xengine".
  refresh_policy	STRING	MASTER PREFER_TPRO PREFER_IMCI FORCE_TPRO FORCE_IMCI COST	Specifies the push-down policy for the full refresh task. The value is case-insensitive. An empty string or an unknown value falls back to the default policy specified by the system variable. MASTER: refresh on the primary node. PREFER_TPRO: prefer to refresh on a row-store read-only (RO) node. Refresh on the primary node if no row-store RO node exists. PREFER_IMCI: prefer to refresh on a column-store index RO node. Refresh on the primary node if no column-store index RO node exists. FORCE_TPRO: force refresh on a row-store RO node. Report an error if no row-store RO node exists. FORCE_IMCI: force refresh on a column-store index RO node. Report an error if no column-store index RO node exists. COST: choose between a row-store RO node and a column-store index RO node based on query cost to execute the refresh.
  unique_check	INT	1 0	Used for XEngine column-store container tables. 0 relaxes the uniqueness check during refresh. 1 or omitting the field keeps the default check.

  Note

  engine, table_format, refresh_policy, and unique_check are supported only on MySQL 8.0.2 for now.

• Notes:

  • MATERIALIZED OPTIONS = '' is treated as no extended parameters being set.

  • When SHOW CREATE TABLE outputs the materialized view definition, the configured MATERIALIZED OPTIONS clause is included.

  • The syntax layer only accepts a string. If subsequent parsing fails, a WARNING is recorded and processing continues with the default behavior, as if no corresponding parameter were parsed.

• Example:

  CREATE MATERIALIZED VIEW mv1
    REFRESH COMPLETE
    ON DEMAND
    START WITH now()
    NEXT now() + INTERVAL 1 hour
    MATERIALIZED OPTIONS = '{"engine":"xengine","table_format":"column","order_key":"col1"}'
    AS SELECT col1, col2 FROM tbl1;

Example

1. Create the table1 and table2 tables to use as base tables for the materialized view.

   -- Create the base tables.
   CREATE TABLE table1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   CREATE TABLE table2 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   
   -- Insert data into the base tables.
   INSERT INTO table1 VALUES (1, 'A', 100), (2, 'B', 200), (3, 'C', 300), (4, 'D', 400);
   INSERT INTO table2 VALUES (1, 'X', 10), (2, 'Y', 20), (3, 'Z', 30), (5, 'W', 50);

2. Create a materialized view.

   CREATE MATERIALIZED VIEW mv1
   REFRESH COMPLETE
   ON DEMAND
   START WITH now() NEXT now() + INTERVAL 1 hour
   AS
   SELECT
       SUM(t1.col3) AS sum_value,
       AVG(t1.col3) AS avg_value
   FROM table1 t1
   JOIN table2 t2 ON t1.col1 = t2.col1;

3. Create a materialized view with mv_options.

   CREATE MATERIALIZED VIEW mv2
     REFRESH COMPLETE
     ON DEMAND
     START WITH now()
     NEXT now() + INTERVAL 1 hour
     MATERIALIZED OPTIONS = '{"engine":"xengine","table_format":"column","refresh_policy":"FORCE_IMCI"}'
     AS
       SELECT t1.col1, t1.col2, t1.col3
       FROM table1 t1 JOIN table2 t2 ON t1.col1 = t2.col1;

Syntax

DROP MATERIALIZED VIEW <view_name>;

Example

DROP MATERIALIZED VIEW mv1;

View task queue

You can query the information_schema.materialized_view_refresh_queue system view to view the refresh queue.

SELECT * FROM information_schema.materialized_view_refresh_queue;

Pause task queue

STOP MATERIALIZED VIEW task FOR TABLE mv1;

STOP MATERIALIZED VIEW task FOR DATABASE mv1;

STOP MATERIALIZED VIEW task FOR ALL;

Restart task queue

RESTART MATERIALIZED VIEW task FOR TABLE mv1;

RESTART MATERIALIZED VIEW task FOR DATABASE mv1;

RESTART MATERIALIZED VIEW task FOR ALL;