Help Center/ Data Warehouse Service / Standard Data Warehouse (9.1.0.x)/ DWS Materialized Views/ Asynchronous Materialized Views/ Materialized View Query Rewriting/ Text Matching Query Rewriting
Updated on 2025-10-09 GMT+08:00
View PDF

Text Matching Query Rewriting

DWS supports text matching query rewriting, including complex queries such as JOIN, AGG, CTE, and subqueries.

Constraints

  • Subqueries of a query can also use materialized views for text matching.
  • If the query statement contains ORDER BY, the ORDER BY column must exist in the output column of the materialized view.
  • The distinctions between a query and a materialized view can include spaces, line breaks, and comments.
  • If an alias is assigned to the output column of a materialized view, the same alias must be used in the query for proper matching.
  • Queries in materialized views cannot contain FOR UPDATE.

Examples

Take tpch Q1 as an example. When a materialized view is generated from the intricate query in Q1, the query plan indicates that the statement has been modified to access the materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
CREATE MATERIALIZED VIEW mv_tpch_q1 enable query rewrite as 
SELECT
        l_returnflag,
        l_linestatus,
        sum(l_quantity) AS sum_qty,
        sum(l_extendedprice) AS sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        avg(l_quantity) AS avg_qty,
        avg(l_extendedprice) AS avg_price,
        avg(l_discount) AS avg_disc,
        count(*) AS count_order
FROM
        lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '3 day'
GROUP BY l_returnflag,l_linestatus
ORDER BY sum_qty;

explain (costs off)
SELECT
        l_returnflag,
        l_linestatus,
        sum(l_quantity) AS sum_qty,
        sum(l_extendedprice) AS sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
        avg(l_quantity) AS avg_qty,
        avg(l_extendedprice) AS avg_price,
        avg(l_discount) AS avg_disc,
        count(*) AS count_order
FROM
        lineitem
WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '3 day'
GROUP BY l_returnflag,l_linestatus
ORDER BY sum_qty;
                 QUERY PLAN
--------------------------------------------
  id |              operation
 ----+-------------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Sort
   3 |       ->  Seq Scan on mv_tpch_q1

If the ORDER BY section aligns with the materialized view definition, complete matching can also be executed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
CREATE TABLE t1 (a int,b int);
CREATE MATERIALIZED VIEW mv_t1 enable query rewrite AS SELECT sum(a), b FROM t1 GROUP BY b;

EXPLAIN (costs off) SELECT sum(a), b FROM t1 GROUP BY b ORDER BY 1;
             QUERY PLAN
-------------------------------------
  id |          operation
 ----+------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Sort
   3 |       ->  Seq Scan on mv_t1
Parent Topic: Materialized View Query Rewriting