文本匹配查询重写

DWS支持文本匹配查询重写，包括JOIN、AGG、CTE、子查询等复杂查询。

使用约束

查询的子查询也可以使用物化视图进行文本匹配。
如果查询语句有ORDER BY，要求ORDER BY列在物化视图输出列中存在才可以进行重写。
支持查询和物化视图间有空格、回车、注释的差异。
物化视图输出列指定别名时，需要查询也指定别名才能匹配成功。
物化视图中的查询不能包含FOR UPDATE。

示例

以tpch Q1为例，基于Q1的复杂查询创建物化视图，查看查询计划时，显示语句已经被重写成了对物化视图的扫描。

     
      
        
        
          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

         

       

     
    

查询除了ORDER BY部分如果与物化视图定义一致，也可以进行全文匹配。

     
          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