AnalyticDB:Query rewrite of materialized views

FILTER

When the query predicate is narrower than the materialized view's predicate, the engine adds a WHERE clause to the materialized view scan to apply the missing filter.

Query

SELECT
  l.shipmode,
  l.extendedprice * (1 - l.discount) AS disc_price
FROM orders AS o, lineitem AS l
WHERE o.orderkey = l.orderkey
  AND l.shipmode IN ('REG AIR', 'TRUCK')
  AND l.commitdate < l.receiptdate
  AND l.shipdate < l.commitdate;

Materialized view

CREATE MATERIALIZED VIEW mv1
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.shipmode,
  l.extendedprice,
  l.discount
FROM orders AS o, lineitem AS l
WHERE o.orderkey = l.orderkey
  AND l.commitdate < l.receiptdate
  AND l.shipdate < l.commitdate;

Rewritten query

SELECT
  shipmode,
  extendedprice * (1 - discount) AS disc_price,
  discount
FROM mv1
WHERE shipmode IN ('REG AIR', 'TRUCK');

JOIN

When the query and the materialized view have different join relationships, the engine derives the required join from the materialized view. For example, a materialized view with an outer join can satisfy a query that needs an inner join by filtering out null rows.

Supported join types: inner join, outer join, left join, and right join.

Query

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey
  AND p.type NOT LIKE 'MEDIUM POLISHED%';

Materialized view

CREATE MATERIALIZED VIEW mv2
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM partsupp AS ps
INNER JOIN part AS p ON p.partkey = ps.partkey
WHERE p.type NOT LIKE 'MEDIUM POLISHED%';

Rewritten query

SELECT type, partkey, suppkey
FROM mv2;

AGGREGATION

When the query or the materialized view uses different GROUP BY clauses or aggregate functions, the engine constructs the same aggregate function from the materialized view using the AGGREGATION rule.

Query

SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

Materialized view

CREATE MATERIALIZED VIEW mv3
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

Rewritten query

SELECT returnflag, linestatus, sum_disc_price, count_order
FROM mv3;

AGGREGATION ROLLUP

When the query groups by a subset of the materialized view's GROUP BY fields, the engine rolls up the precomputed aggregates.

Query

SELECT
  l.returnflag,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.returnflag = 'R'
GROUP BY l.returnflag;

Materialized view

CREATE MATERIALIZED VIEW mv4
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.returnflag,
  l.linestatus,
  SUM(l.extendedprice * (1 - l.discount)) AS sum_disc_price,
  COUNT(*) AS count_order
FROM lineitem AS l
GROUP BY l.returnflag, l.linestatus;

Rewritten query

SELECT
  returnflag,
  linestatus,
  sum_disc_price,
  count_order
FROM mv4
WHERE returnflag = 'R'
GROUP BY returnflag;

SUBQUERIES

When the query uses a subquery in place of a base table, the engine checks whether the materialized view covers the full table and pushes the subquery's filter into the rewritten scan.

Query

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p,
  (SELECT * FROM partsupp WHERE suppkey > 10) ps
WHERE p.partkey = ps.partkey;

Materialized view

CREATE MATERIALIZED VIEW mv5
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey;

Rewritten query

SELECT type, partkey, suppkey
FROM mv5
WHERE suppkey > 10;

QUERY PARTIAL

When the query references a table that the materialized view doesn't cover, the engine joins the materialized view with the missing table.

Query

SELECT
  p.type,
  p.partkey,
  ps.suppkey
FROM part AS p, partsupp AS ps
WHERE p.partkey = ps.partkey
  AND p.type NOT LIKE 'MEDIUM POLISHED%';

Materialized view

CREATE MATERIALIZED VIEW mv6
REFRESH NEXT now() + INTERVAL 1 day
ENABLE QUERY REWRITE
AS
SELECT
  p.type,
  p.partkey
FROM part AS p
WHERE p.type NOT LIKE 'MEDIUM POLISHED%';

Rewritten query

SELECT
  mv6.type,
  mv6.partkey,
  ps.suppkey
FROM mv6, partsupp AS ps
WHERE mv6.partkey = ps.partkey;

UNION

When the materialized view covers only part of the query's date or value range, the engine retrieves the covered portion from the materialized view and fetches the remaining rows directly from the base table, then combines the results with UNION ALL.

Query

SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.shipdate >= DATE '1998-01-01'
GROUP BY l.linestatus;

Materialized view (covers shipdate >= 2000-01-01 only)

CREATE MATERIALIZED VIEW mv7
REFRESH NEXT now() + interval 1 day
ENABLE QUERY REWRITE
AS
SELECT
  l.linestatus,
  COUNT(*) AS count_order
FROM lineitem AS l
WHERE l.shipdate >= DATE '2000-01-01'
GROUP BY l.linestatus;

Rewritten query

SELECT linestatus, count_order
FROM (
    SELECT linestatus, count_order
    FROM mv7
  UNION ALL
    SELECT
      l.linestatus,
      COUNT(*) AS count_order
    FROM lineitem AS l
    WHERE l.shipdate >= DATE '1998-01-01' AND l.shipdate < DATE '1998-01-01'
    GROUP BY l.linestatus
)
GROUP BY linestatus;