AnalyticDB:Penulisan ulang kueri tampilan yang di-materialisasi

FILTER

Ketika predikat kueri lebih sempit daripada predikat materialized view, mesin menambahkan klausa WHERE ke pemindaian materialized view untuk menerapkan filter yang hilang. Jika suatu ekspresi dalam kueri tidak ada dalam materialized view, mesin juga berusaha menghitung ekspresi tersebut dari view tersebut.

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

Ketika kueri dan materialized view memiliki hubungan join yang berbeda, mesin menurunkan join yang diperlukan dari materialized view. Misalnya, materialized view dengan outer join dapat memenuhi kueri yang membutuhkan inner join dengan memfilter baris null.

Jenis join yang didukung: inner join, outer join, left join, dan 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

Ketika kueri atau materialized view menggunakan klausa GROUP BY atau fungsi agregat yang berbeda, mesin membangun fungsi agregat yang sama dari materialized view menggunakan aturan AGGREGATION.

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

Ketika kueri melakukan pengelompokan berdasarkan subset bidang GROUP BY materialized view, mesin melakukan roll-up terhadap agregat yang telah diproses sebelumnya.

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

Ketika kueri menggunakan subkueri sebagai pengganti tabel dasar, mesin memeriksa apakah materialized view mencakup seluruh tabel dan mendorong filter subkueri ke dalam pemindaian yang ditulis ulang.

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

Ketika kueri mereferensikan tabel yang tidak dicakup oleh materialized view, mesin melakukan join antara materialized view dengan tabel yang hilang tersebut.

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

Ketika materialized view hanya mencakup sebagian rentang tanggal atau nilai kueri, mesin mengambil bagian yang tercakup dari materialized view dan mengambil baris sisanya langsung dari tabel dasar, lalu menggabungkan hasilnya dengan 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 (hanya mencakup shipdate >= 2000-01-01)

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;