Overview

Scenarios

Materialized view query rewriting can avoid complex association, aggregation, and expression calculation operations in the query.

• Text matching query rewriting allows queries to match the definition of a materialized view, even if the format and comments differ. It supports complex queries like CTE and window functions. If a complex query performs poorly, creating a materialized view with the same statement can speed up the query.
• Structure matching query rewriting can match multiple statements, which is useful when the relationship between tables remains constant but filter criteria and output columns vary.

By default, query rewriting can be performed only when materialized views are valid. You can also set the force_rewrite_timeout parameter in the CREATE MATERIALIZED VIEW syntax to specify a period during which a materialized view can still support query writing even if it is invalid.

Query Rewriting Methods

DWS provides the following two methods for materialized view query rewriting:

• Text matching query rewriting

  When the query matches the SQL or syntax tree of the materialized view, you can use the materialized view to rewrite the query.

• Structure matching query rewriting

  This method extracts the specific structure of the query for rewriting. Currently, the projection, filter criteria, join, and aggregation parts of a query are matched and rewritten with materialized views.

Setting Materialized View Query Rewriting

The GUC parameter mv_rewrite_rule of DWS specifies the query rewriting mode.