AnalyticDB:Query rewrite of materialized views

FILTER

If the predicate of a query statement is different from the predicate of a materialized view, AnalyticDB for MySQL uses the FILTER rule to construct a compensation expression. If the expression that is contained in the query statement does not exist in the materialized view, AnalyticDB for MySQL attempts to calculate the expression in the materialized view. Example:

• Execute a query statement.

  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;

• Create a 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;

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

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

JOIN

AnalyticDB for MySQL supports inner joins, outer joins, left joins, and right joins for queries and materialized views. If the query and the materialized view contains different join relationships, AnalyticDB for MySQL attempts to calculate the join relationship required for the query based on the materialized view by using the JOIN rule. For example, AnalyticDB for MySQL filters a row of data in materialized views. This way, materialized views that contain outer joins can be used to calculate queries that contain inner join relationships. Example:

• Execute a query statement.

  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%';

• Create a 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%';

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

  SELECT type, partkey, suppkey
  FROM mv2;

AGGREGATION

If the query or the materialized view uses different GROUP BY clauses or aggregate functions, AnalyticDB for MySQL constructs the same aggregate function from the materialized view by using the AGGREGATION rule. Example:

• Execute a query statement.

  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;

• Create a 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;

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

  SELECT returnflag, linestatus, sum_disc_price, count_order
  FROM mv3;

AGGREGATION ROLLUP

If the query and the materialized view use different GROUP BY fields, AnalyticDB for MySQL attempts to roll up the materialized view by using the ROLLUP rule. Example:

• Execute a query statement.

  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;

• Create a 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;

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

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

SUBQUERIES

If the query and the materialized view have different subqueries, AnalyticDB for MySQL attempts to rewrite the query by using the SUBQUERIES rule. Example:

• Execute a query statement.

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

• Create a 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;

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

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

QUERY PARTIAL

If the table involved in the query does not exist in the materialized view, AnalyticDB for MySQL attempts to join the missing table to the materialized view by using the QUERY PARTIAL rule. Example:

• Execute a query statement.

  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%';

• Create a 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%';

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

  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%';

UNION

If the materialized view contains only specific data required for the query, AnalyticDB for MySQL obtains specific query results from existing data of the materialized view and the remaining query results from base tables by using the UNION rule.

• Execute a query statement.

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

• Create a materialized view.

  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;

• If you execute the query statement after you enable the query rewrite feature for the materialized view, the query statement is rewritten as the following statement:

  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;