E-MapReduce:Penulisan ulang kueri menggunakan tampilan yang di-materialisasi

Penulisan ulang Kueri Delta Join​

Query Delta Join mengacu pada situasi di mana tabel yang digabungkan dalam kueri merupakan superset dari tabel yang digabungkan dalam tampilan materialisasi. Sebagai contoh, kueri berikut menggabungkan tabel lineorder, customer, dan part. Jika tampilan materialisasi join_mv1 hanya mencakup gabungan lineorder dan customer, StarRocks dapat menggunakan join_mv1 untuk menulis ulang kueri.

Contoh:

SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address, p_name
FROM
    lineorder INNER JOIN customer ON lo_custkey = c_custkey
    INNER JOIN part ON lo_partkey = p_partkey;

Rencana kueri asli dan rencana yang telah ditulis ulang adalah sebagai berikut:

Penulisan Ulang Tampilan Delta Join​

View Delta Join mengacu pada situasi di mana tabel yang digabungkan dalam kueri merupakan subset dari tabel yang digabungkan dalam tampilan materialisasi. Fitur ini sering digunakan dalam skenario yang melibatkan tabel lebar besar. Sebagai contoh, dalam konteks Star Schema Benchmark (SSB), Anda dapat membuat tampilan materialisasi dengan menggabungkan semua tabel untuk meningkatkan kinerja kueri. Pengujian menunjukkan bahwa setelah kueri ditulis ulang secara transparan melalui tampilan materialisasi, kinerja kueri gabungan multi-tabel dapat mencapai tingkat yang sama dengan menanyakan tabel lebar besar yang sesuai.

Untuk mengaktifkan penulisan ulang View Delta Join, pastikan tampilan materialisasi mencakup Gabungan Preservasi Kardinalitas 1:1 dengan semua tabel yang relevan dalam kueri. Sembilan jenis gabungan berikut, yang memenuhi kondisi kendala, dianggap sebagai Gabungan Preservasi Kardinalitas dan dapat digunakan untuk mengaktifkan penulisan ulang View Delta Join.

Menggunakan pengujian SSB sebagai contoh, buat database dan tabel dasar berikut.

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;
CREATE TABLE customer (
  c_custkey         INT(11)       NOT NULL,
  c_name            VARCHAR(26)   NOT NULL,
  c_address         VARCHAR(41)   NOT NULL,
  c_city            VARCHAR(11)   NOT NULL,
  c_nation          VARCHAR(16)   NOT NULL,
  c_region          VARCHAR(13)   NOT NULL,
  c_phone           VARCHAR(16)   NOT NULL,
  c_mktsegment      VARCHAR(11)   NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(c_custkey)
DISTRIBUTED BY HASH(c_custkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "c_custkey"   -- Tentukan kunci unik.
);

CREATE TABLE dates (
  d_datekey          DATE          NOT NULL,
  d_date             VARCHAR(20)   NOT NULL,
  d_dayofweek        VARCHAR(10)   NOT NULL,
  d_month            VARCHAR(11)   NOT NULL,
  d_year             INT(11)       NOT NULL,
  d_yearmonthnum     INT(11)       NOT NULL,
  d_yearmonth        VARCHAR(9)    NOT NULL,
  d_daynuminweek     INT(11)       NOT NULL,
  d_daynuminmonth    INT(11)       NOT NULL,
  d_daynuminyear     INT(11)       NOT NULL,
  d_monthnuminyear   INT(11)       NOT NULL,
  d_weeknuminyear    INT(11)       NOT NULL,
  d_sellingseason    VARCHAR(14)   NOT NULL,
  d_lastdayinweekfl  INT(11)       NOT NULL,
  d_lastdayinmonthfl INT(11)       NOT NULL,
  d_holidayfl        INT(11)       NOT NULL,
  d_weekdayfl        INT(11)       NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(d_datekey)
DISTRIBUTED BY HASH(d_datekey) BUCKETS 1
PROPERTIES (
"unique_constraints" = "d_datekey"   -- Tentukan kunci unik.
);

CREATE TABLE supplier (
  s_suppkey          INT(11)       NOT NULL,
  s_name             VARCHAR(26)   NOT NULL,
  s_address          VARCHAR(26)   NOT NULL,
  s_city             VARCHAR(11)   NOT NULL,
  s_nation           VARCHAR(16)   NOT NULL,
  s_region           VARCHAR(13)   NOT NULL,
  s_phone            VARCHAR(16)   NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(s_suppkey)
DISTRIBUTED BY HASH(s_suppkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "s_suppkey"   -- Tentukan kunci unik.
);

CREATE TABLE part (
  p_partkey          INT(11)       NOT NULL,
  p_name             VARCHAR(23)   NOT NULL,
  p_mfgr             VARCHAR(7)    NOT NULL,
  p_category         VARCHAR(8)    NOT NULL,
  p_brand            VARCHAR(10)   NOT NULL,
  p_color            VARCHAR(12)   NOT NULL,
  p_type             VARCHAR(26)   NOT NULL,
  p_size             TINYINT(11)   NOT NULL,
  p_container        VARCHAR(11)   NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(p_partkey)
DISTRIBUTED BY HASH(p_partkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "p_partkey"   -- Tentukan kunci unik.
);

CREATE TABLE lineorder (
  lo_orderdate       DATE          NOT NULL, -- Tentukan sebagai NOT NULL.
  lo_orderkey        INT(11)       NOT NULL,
  lo_linenumber      TINYINT       NOT NULL,
  lo_custkey         INT(11)       NOT NULL, -- Tentukan sebagai NOT NULL.
  lo_partkey         INT(11)       NOT NULL, -- Tentukan sebagai NOT NULL.
  lo_suppkey         INT(11)       NOT NULL, -- Tentukan sebagai NOT NULL.
  lo_orderpriority   VARCHAR(100)  NOT NULL,
  lo_shippriority    TINYINT       NOT NULL,
  lo_quantity        TINYINT       NOT NULL,
  lo_extendedprice   INT(11)       NOT NULL,
  lo_ordtotalprice   INT(11)       NOT NULL,
  lo_discount        TINYINT       NOT NULL,
  lo_revenue         INT(11)       NOT NULL,
  lo_supplycost      INT(11)       NOT NULL,
  lo_tax             TINYINT       NOT NULL,
  lo_commitdate      DATE          NOT NULL,
  lo_shipmode        VARCHAR(100)  NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(lo_orderdate,lo_orderkey)
PARTITION BY RANGE(lo_orderdate)
(PARTITION p1 VALUES [("0000-01-01"), ("1993-01-01")),
PARTITION p2 VALUES [("1993-01-01"), ("1994-01-01")),
PARTITION p3 VALUES [("1994-01-01"), ("1995-01-01")),
PARTITION p4 VALUES [("1995-01-01"), ("1996-01-01")),
PARTITION p5 VALUES [("1996-01-01"), ("1997-01-01")),
PARTITION p6 VALUES [("1997-01-01"), ("1998-01-01")),
PARTITION p7 VALUES [("1998-01-01"), ("1999-01-01")))
DISTRIBUTED BY HASH(lo_orderkey) BUCKETS 48
PROPERTIES (
"foreign_key_constraints" = "(
    (lo_custkey) REFERENCES customer(c_custkey),
    (lo_partkey) REFERENCES part(p_partkey),
    (lo_suppkey) REFERENCES supplier(s_suppkey)
    )"  -- Tentukan kendala kunci asing.
);

Buat tampilan materialisasi lineorder_flat_mv yang menggabungkan tabel lineorder, customer, supplier, part, dan dates.

USE test_db;
CREATE MATERIALIZED VIEW lineorder_flat_mv
DISTRIBUTED BY HASH(LO_ORDERDATE, LO_ORDERKEY) BUCKETS 48
PARTITION BY LO_ORDERDATE
REFRESH MANUAL
PROPERTIES (
    "partition_refresh_number"="1"
)
AS SELECT /*+ SET_VAR(query_timeout = 7200) */     -- Setel batas waktu refresh.
       l.LO_ORDERDATE        AS LO_ORDERDATE,
       l.LO_ORDERKEY         AS LO_ORDERKEY,
       l.LO_LINENUMBER       AS LO_LINENUMBER,
       l.LO_CUSTKEY          AS LO_CUSTKEY,
       l.LO_PARTKEY          AS LO_PARTKEY,
       l.LO_SUPPKEY          AS LO_SUPPKEY,
       l.LO_ORDERPRIORITY    AS LO_ORDERPRIORITY,
       l.LO_SHIPPRIORITY     AS LO_SHIPPRIORITY,
       l.LO_QUANTITY         AS LO_QUANTITY,
       l.LO_EXTENDEDPRICE    AS LO_EXTENDEDPRICE,
       l.LO_ORDTOTALPRICE    AS LO_ORDTOTALPRICE,
       l.LO_DISCOUNT         AS LO_DISCOUNT,
       l.LO_REVENUE          AS LO_REVENUE,
       l.LO_SUPPLYCOST       AS LO_SUPPLYCOST,
       l.LO_TAX              AS LO_TAX,
       l.LO_COMMITDATE       AS LO_COMMITDATE,
       l.LO_SHIPMODE         AS LO_SHIPMODE,
       c.C_NAME              AS C_NAME,
       c.C_ADDRESS           AS C_ADDRESS,
       c.C_CITY              AS C_CITY,
       c.C_NATION            AS C_NATION,
       c.C_REGION            AS C_REGION,
       c.C_PHONE             AS C_PHONE,
       c.C_MKTSEGMENT        AS C_MKTSEGMENT,
       s.S_NAME              AS S_NAME,
       s.S_ADDRESS           AS S_ADDRESS,
       s.S_CITY              AS S_CITY,
       s.S_NATION            AS S_NATION,
       s.S_REGION            AS S_REGION,
       s.S_PHONE             AS S_PHONE,
       p.P_NAME              AS P_NAME,
       p.P_MFGR              AS P_MFGR,
       p.P_CATEGORY          AS P_CATEGORY,
       p.P_BRAND             AS P_BRAND,
       p.P_COLOR             AS P_COLOR,
       p.P_TYPE              AS P_TYPE,
       p.P_SIZE              AS P_SIZE,
       p.P_CONTAINER         AS P_CONTAINER,
       d.D_DATE              AS D_DATE,
       d.D_DAYOFWEEK         AS D_DAYOFWEEK,
       d.D_MONTH             AS D_MONTH,
       d.D_YEAR              AS D_YEAR,
       d.D_YEARMONTHNUM      AS D_YEARMONTHNUM,
       d.D_YEARMONTH         AS D_YEARMONTH,
       d.D_DAYNUMINWEEK      AS D_DAYNUMINWEEK,
       d.D_DAYNUMINMONTH     AS D_DAYNUMINMONTH,
       d.D_DAYNUMINYEAR      AS D_DAYNUMINYEAR,
       d.D_MONTHNUMINYEAR    AS D_MONTHNUMINYEAR,
       d.D_WEEKNUMINYEAR     AS D_WEEKNUMINYEAR,
       d.D_SELLINGSEASON     AS D_SELLINGSEASON,
       d.D_LASTDAYINWEEKFL   AS D_LASTDAYINWEEKFL,
       d.D_LASTDAYINMONTHFL  AS D_LASTDAYINMONTHFL,
       d.D_HOLIDAYFL         AS D_HOLIDAYFL,
       d.D_WEEKDAYFL         AS D_WEEKDAYFL
   FROM lineorder            AS l
       INNER JOIN customer   AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
       INNER JOIN supplier   AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
       INNER JOIN part       AS p ON p.P_PARTKEY = l.LO_PARTKEY
       INNER JOIN dates      AS d ON l.LO_ORDERDATE = d.D_DATEKEY;    

Contoh SSB Q2.1 adalah sebagai berikut:

USE test_db;
SELECT
    SUM(lo_revenue) AS lo_revenue,
    d_year,
    p_brand
FROM lineorder
    JOIN dates ON lo_orderdate = d_datekey
    JOIN part ON lo_partkey = p_partkey
    JOIN supplier ON lo_suppkey = s_suppkey
WHERE p_category = 'MFGR#12' AND s_region = 'AMERICA'
GROUP BY d_year, p_brand
ORDER BY d_year, p_brand;

SSB Q2.1 melibatkan penggabungan empat tabel, tetapi dibandingkan dengan tampilan materialisasi lineorder_flat_mv, tabel customer tidak termasuk. Dalam lineorder_flat_mv, operasi lineorder INNER JOIN customer pada dasarnya merupakan Gabungan Preservasi Kardinalitas. Oleh karena itu, secara logis, gabungan ini dapat dihilangkan tanpa memengaruhi hasil kueri. Akibatnya, Q2.1 dapat ditulis ulang menggunakan lineorder_flat_mv.

Rencana kueri asli dan rencana yang ditulis ulang adalah sebagai berikut.

Demikian pula, kueri lain dalam SSB juga dapat ditulis ulang secara transparan menggunakan lineorder_flat_mv.

Penulisan ulang Derivasi Join

Derivasi Join mengacu pada situasi di mana jenis gabungan dalam tampilan materialisasi dan kueri tidak konsisten, tetapi hasil gabungan tampilan materialisasi mencakup hasil gabungan kueri.

Saat ini, dua skenario berikut didukung:

• Gabungan tiga tabel atau lebih

• Gabungan dua tabel

USE test_db;
CREATE MATERIALIZED VIEW join_mv5
PARTITION BY lo_orderdate
DISTRIBUTED BY hash(lo_orderkey)
PROPERTIES (
  "partition_refresh_number" = "1"
)
AS
SELECT lo_orderkey, lo_orderdate, lo_linenumber, lo_revenue, c_custkey, c_address, p_name
FROM customer LEFT OUTER JOIN lineorder
ON c_custkey = lo_custkey
INNER JOIN part
ON p_partkey = lo_partkey;

USE test_db;
SELECT lo_orderkey, lo_orderdate, lo_linenumber, lo_revenue, c_custkey, c_address, p_name
FROM customer INNER JOIN lineorder
ON c_custkey = lo_custkey
INNER JOIN part
ON p_partkey = lo_partkey;

USE test_db;
CREATE MATERIALIZED VIEW join_mv3
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder LEFT OUTER JOIN customer
ON lo_custkey = c_custkey;

USE test_db;
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;

USE test_db;
CREATE MATERIALIZED VIEW join_mv4
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;

USE test_db;
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder LEFT OUTER JOIN customer
ON lo_custkey = c_custkey
WHERE customer.c_address = "Sb4gxKs7";

Penulisan ulang Rollup Agregasi

StarRocks mendukung penulisan ulang kueri melalui rollup agregasi, yang berarti StarRocks dapat menggunakan tampilan materialisasi asinkron yang dibuat dengan klausa GROUP BY a,b untuk menulis ulang kueri agregasi dengan klausa GROUP BY a. Sebagai contoh, agg_mv1 dapat menulis ulang kueri berikut.

USE test_db;
SELECT 
  lo_orderkey, 
  c_name, 
  sum(lo_revenue) AS total_revenue, 
  max(lo_discount) AS max_discount 
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey
GROUP BY lo_orderkey, c_name;

Rencana kueri asli dan rencana yang telah ditulis ulang adalah sebagai berikut.

Hanya beberapa fungsi agregasi yang mendukung penulisan ulang kueri rollup agregasi.

Agregasi DISTINCT tanpa kolom GROUP BY yang sesuai tidak dapat memanfaatkan penulisan ulang kueri rollup agregasi. Namun, sejak StarRocks v3.1, kueri dengan fungsi agregasi DISTINCT yang tidak memiliki kolom GROUP BY tetapi mencakup predikat ekuivalen dapat ditulis ulang oleh tampilan materialisasi terkait, karena StarRocks mampu mengonversi predikat ekuivalen menjadi ekspresi GROUP BY konstan.

Dalam contoh berikut, StarRocks dapat menggunakan tampilan materialisasi order_agg_mv1 untuk menulis ulang query yang sesuai.

USE test_db;
CREATE MATERIALIZED VIEW order_agg_mv1
DISTRIBUTED BY HASH(`order_id`) BUCKETS 12
REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
AS
SELECT
    order_date,
    count(distinct client_id) 
FROM order_list 
GROUP BY order_date;


-- Kueri
USE test_db;
SELECT
    order_date,
    count(distinct client_id) 
FROM order_list WHERE order_date='2023-07-03';

Pendorongan Agregasi

Mulai versi 3.3.0, StarRocks mendukung fitur pushdown agregasi untuk penulisan ulang kueri menggunakan tampilan yang di-materialisasi. Saat fitur ini diaktifkan, fungsi agregasi diteruskan ke Scan Operator selama eksekusi kueri dan ditulis ulang oleh tampilan yang di-materialisasi sebelum Join Operator dieksekusi. Hal ini mengurangi ekspansi data akibat operasi join, sehingga meningkatkan performa kueri.

Fitur ini dinonaktifkan secara default. Untuk mengaktifkannya, atur variabel sistem enable_materialized_view_agg_pushdown_rewrite menjadi true.

Misalkan Anda perlu mempercepat kueri berbasis SSB berikut: SQL1.

-- SQL1
USE test_db;
SELECT 
    LO_ORDERDATE, sum(LO_REVENUE), max(LO_REVENUE), count(distinct LO_REVENUE)
FROM lineorder l JOIN dates d 
ON l.LO_ORDERDATE = d.d_date 
GROUP BY LO_ORDERDATE 
ORDER BY LO_ORDERDATE;

SQL1 mencakup agregasi pada tabel lineorder serta gabungan antara tabel lineorder dan dates. Agregasi dilakukan pada tabel lineorder, sedangkan gabungan dengan tabel dates hanya digunakan untuk penyaringan data. Oleh karena itu, SQL1 secara logis setara dengan SQL2 berikut.

-- SQL2
USE test_db;
SELECT 
    LO_ORDERDATE, sum(sum1), max(max1), bitmap_union_count(bitmap1)
FROM 
 (SELECT
  LO_ORDERDATE,  sum(LO_REVENUE) AS sum1, max(LO_REVENUE) AS max1, bitmap_union(to_bitmap(LO_REVENUE)) AS bitmap1
  FROM lineorder 
  GROUP BY LO_ORDERDATE) l JOIN dates d 
ON l.LO_ORDERDATE = d.d_date 
GROUP BY LO_ORDERDATE 
ORDER BY LO_ORDERDATE;

SQL2 memindahkan agregasi ke tahap awal, sehingga secara signifikan mengurangi jumlah data untuk join. Anda dapat membuat tampilan materialisasi berdasarkan subquery dalam SQL2 dan mengaktifkan pendorongan agregasi untuk menulis ulang serta mempercepat proses agregasi.

-- Buat tampilan materialisasi mv0
USE test_db;
CREATE MATERIALIZED VIEW mv0 REFRESH MANUAL AS
SELECT
  LO_ORDERDATE, 
  sum(LO_REVENUE) AS sum1, 
  max(LO_REVENUE) AS max1, 
  bitmap_union(to_bitmap(LO_REVENUE)) AS bitmap1
FROM lineorder 
GROUP BY LO_ORDERDATE;

-- Aktifkan pendorongan agregasi
SET enable_materialized_view_agg_pushdown_rewrite=true;

Pada titik ini, SQL1 akan ditulis ulang dan dipercepat melalui tampilan materialisasi.

USE test_db;
SELECT 
    LO_ORDERDATE, sum(sum1), max(max1), bitmap_union_count(bitmap1)
FROM 
 (SELECT LO_ORDERDATE, sum1, max1, bitmap1 FROM mv0) l JOIN dates d 
ON l.LO_ORDERDATE = d.d_date 
GROUP BY LO_ORDERDATE
ORDER BY LO_ORDERDATE;

Perhatikan bahwa hanya beberapa fungsi agregasi yang mendukung penulisan ulang rollup agregasi yang dapat didorong.

• MIN

• MAX

• COUNT

• COUNT DISTINCT

• SUM

• BITMAP_UNION

• HLL_UNION

• PERCENTILE_UNION

• BITMAP_AGG

• ARRAY_AGG_DISTINCT

Penulisan ulang COUNT DISTINCT

StarRocks mendukung penulisan ulang perhitungan COUNT DISTINCT sebagai perhitungan tipe BITMAP, memungkinkan deduplikasi akurat berperforma tinggi menggunakan tampilan materialisasi.

USE test_db;
CREATE MATERIALIZED VIEW distinct_mv
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, bitmap_union(to_bitmap(lo_custkey)) AS distinct_customer
FROM lineorder
GROUP BY lo_orderkey;

Tampilan materialisasi ini dapat menulis ulang kueri berikut.

USE test_db;
SELECT lo_orderkey, count(distinct lo_custkey) 
FROM lineorder 
GROUP BY lo_orderkey;

Penulisan ulang Predicate Union

Ketika rentang predikat tampilan materialisasi merupakan subset dari rentang predikat kueri, kueri dapat ditulis ulang menggunakan operasi UNION.

Sebagai contoh, buat tampilan materialisasi berikut ini.

USE test_db;
CREATE MATERIALIZED VIEW agg_mv4
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT 
  lo_orderkey, 
  SUM(lo_revenue) AS total_revenue, 
  MAX(lo_discount) AS max_discount 
FROM lineorder
WHERE lo_orderkey < 300000000
GROUP BY lo_orderkey;

Tampilan materialisasi ini dapat menulis ulang kueri berikut.

USE test_db;
SELECT 
  lo_orderkey, 
  SUM(lo_revenue) AS total_revenue, 
  MAX(lo_discount) AS max_discount 
FROM lineorder
GROUP BY lo_orderkey;

Rencana kueri asli dan rencana yang telah ditulis ulang adalah sebagai berikut.

Di sini, agg_mv4 berisi data di mana lo_orderkey < 300000000, sedangkan data di mana lo_orderkey >= 300000000 diperoleh dengan menanyakan langsung tabel lineorder.

Penulisan ulang Partition Union

Misalkan tampilan materialisasi partisi dibuat berdasarkan tabel partisi. Ketika rentang partisi yang dipindai oleh kueri merupakan superset dari rentang partisi terbaru tampilan materialisasi, kueri dapat ditulis ulang menggunakan operasi UNION.

Sebagai contoh, perhatikan tampilan materialisasi agg_mv5 berikut. Tabel dasar lineorder saat ini mencakup partisi p1 hingga p7, dan tampilan materialisasi juga mencakup partisi p1 hingga p7.

USE test_db;
CREATE MATERIALIZED VIEW agg_mv5
DISTRIBUTED BY hash(lo_orderkey)
PARTITION BY RANGE(lo_orderdate)(
  START ("1993-01-01") END ("1999-01-01") EVERY (INTERVAL 1 YEAR)
)
REFRESH MANUAL
AS
SELECT 
  lo_orderdate, 
  lo_orderkey, 
  sum(lo_revenue) AS total_revenue, 
  max(lo_discount) AS max_discount 
FROM lineorder
GROUP BY lo_orderdate,lo_orderkey;

Jika lineorder menambahkan partisi baru p8 dengan rentang [("19990101"), ("20000101")), maka kueri berikut dapat ditulis ulang menggunakan operasi UNION.

USE test_db;
SELECT 
  lo_orderdate, 
  lo_orderkey, 
  sum(lo_revenue) AS total_revenue, 
  max(lo_discount) AS max_discount 
FROM lineorder
GROUP BY lo_orderdate,lo_orderkey;

Rencana kueri asli dan rencana yang ditulis ulang adalah sebagai berikut.

Seperti yang ditunjukkan di atas, agg_mv5 berisi data dari partisi p1 hingga p7, sedangkan data dari partisi p8 berasal dari lineorder.

Prinsip dasar​

Dalam logika penulisan ulang kueri sebelumnya, StarRocks mengembangkan kueri berbasis tampilan menjadi kueri yang menargetkan tabel dasar tampilan. Jika rencana eksekusi kueri yang diperluas tidak sesuai dengan pola SPJG, tampilan materialisasi tidak dapat digunakan untuk menulis ulang kueri.

Untuk mengatasi masalah ini, StarRocks memperkenalkan operator baru bernama LogicalViewScanOperator, yang menyederhanakan struktur pohon rencana eksekusi tanpa memerlukan perluasan kueri. Dengan demikian, pohon rencana eksekusi kueri lebih mungkin memenuhi pola SPJG, sehingga meningkatkan optimasi penulisan ulang kueri.

Contoh berikut menunjukkan kueri dengan subquery agregasi, tampilan yang dibangun dari subquery, kueri yang diperluas dari tampilan, serta tampilan materialisasi yang dibangun berdasarkan tampilan tersebut.

-- Kueri asli.
SELECT 
  v1.a,
  t2.b,
  v1.total
FROM(
  SELECT 
    a,
    sum(c) AS total
  FROM t1
  GROUP BY a
) v1
INNER JOIN t2 ON v1.a = t2.a;

-- Tampilan yang dibangun berdasarkan subquery.
CREATE VIEW view_1 AS
SELECT 
  t1.a,
  sum(t1.c) AS total
FROM t1
GROUP BY t1.a;
    
-- Kueri berbasis tampilan.
SELECT 
  v1.a,
  t2.b,
  v1.total
FROM view_1 v1
JOIN t2 ON v1.a = t2.a;
    
-- Tampilan materialisasi berbasis tampilan.
CREATE MATERIALIZED VIEW mv1
DISTRIBUTED BY hash(a)
REFRESH MANUAL
AS
SELECT 
  v1.a,
  t2.b,
  v1.total
FROM view_1 v1
JOIN t2 ON v1.a = t2.a;

Rencana eksekusi kueri asli ditampilkan di sisi kiri gambar di bawah ini. Karena LogicalAggregateOperator di dalam JOIN tidak sesuai dengan pola SPJG, StarRocks tidak mendukung penulisan ulang kueri dalam kasus ini. Namun, jika subkueri didefinisikan sebagai sebuah Tampilan, kueri asli dapat diperluas menjadi kueri yang menargetkan Tampilan tersebut. Dengan menggunakan LogicalViewScanOperator, StarRocks dapat mengonversi bagian yang tidak sesuai menjadi pola SPJG, sehingga memungkinkan penulisan ulang kueri.

Item konfigurasi

Secara default, StarRocks menonaktifkan penulisan ulang kueri untuk Tampilan yang di-materialisasi berbasis Tampilan.

Untuk mengaktifkan fitur ini, atur variabel berikut.

SET enable_view_based_mv_rewrite = true;

Skenario penggunaan​

USE test_db;
CREATE VIEW q18_view
AS
SELECT
  c_name,
  c_custkey,
  o_orderkey,
  o_orderdate,
  o_totalprice,
  sum(l_quantity)
FROM
  customer,
  orders,
  lineitem
WHERE
  o_orderkey IN (
    SELECT
      l_orderkey
    FROM
      lineitem
    GROUP BY
      l_orderkey having
        sum(l_quantity) > 315
  )
  AND c_custkey = o_custkey
  AND o_orderkey = l_orderkey
GROUP BY
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice;

CREATE MATERIALIZED VIEW q18_mv
DISTRIBUTED BY hash(c_custkey, o_orderkey)
REFRESH MANUAL
AS
SELECT * FROM q18_view;

USE test_db;
-- SQL1
EXPLAIN LOGICAL SELECT * FROM q18_view;
+-------------------------------------------------------------------------------------------------------+
| Explain String                                                                                        |
+-------------------------------------------------------------------------------------------------------+
| - Output => [2:c_name, 1:c_custkey, 9:o_orderkey, 10:o_orderdate, 13:o_totalprice, 52:sum]            |
|     - SCAN [q18_mv] => [1:c_custkey, 2:c_name, 52:sum, 9:o_orderkey, 10:o_orderdate, 13:o_totalprice] |
|             MaterializedView: true                                                                    |
|             Estimates: {row: 9, cpu: 486.00, memory: 0.00, network: 0.00, cost: 243.00}               |
|             partitionRatio: 1/1, tabletRatio: 96/96                                                   |
|             1:c_custkey := 60:c_custkey                                                               |
|             2:c_name := 59:c_name                                                                     |
|             52:sum := 64:sum(l_quantity)                                                              |
|             9:o_orderkey := 61:o_orderkey                                                             |
|             10:o_orderdate := 62:o_orderdate                                                          |
|             13:o_totalprice := 63:o_totalprice                                                        |
+-------------------------------------------------------------------------------------------------------+

-- SQL2
EXPLAIN LOGICAL SELECT c_name, sum(`sum(l_quantity)`) FROM q18_view GROUP BY c_name;
+-----------------------------------------------------------------------------------------------------+
| Explain String                                                                                      |
+-----------------------------------------------------------------------------------------------------+
| - Output => [2:c_name, 59:sum]                                                                      |
|     - AGGREGATE(GLOBAL) [2:c_name]                                                                  |
|             Estimates: {row: 9, cpu: 306.00, memory: 306.00, network: 0.00, cost: 1071.00}          |
|             59:sum := sum(59:sum)                                                                   |
|         - EXCHANGE(SHUFFLE) [2]                                                                     |
|                 Estimates: {row: 9, cpu: 30.60, memory: 0.00, network: 30.60, cost: 306.00}         |
|             - AGGREGATE(LOCAL) [2:c_name]                                                           |
|                     Estimates: {row: 9, cpu: 61.20, memory: 30.60, network: 0.00, cost: 244.80}     |
|                     59:sum := sum(52:sum)                                                           |
|                 - SCAN [q18_mv] => [2:c_name, 52:sum]                                               |
|                         MaterializedView: true                                                      |
|                         Estimates: {row: 9, cpu: 306.00, memory: 0.00, network: 0.00, cost: 153.00} |
|                         partitionRatio: 1/1, tabletRatio: 96/96                                     |
|                         2:c_name := 60:c_name                                                       |
|                         52:sum := 65:sum(l_quantity)                                                |
+-----------------------------------------------------------------------------------------------------+

USE test_db;
CREATE VIEW view_1
AS
SELECT
  c_custkey,
  c_name,
  c_address,
  c_city,
  c_nation,
  c_region
FROM customer;

CREATE VIEW view_2
AS
SELECT
  lo_orderkey,
  lo_linenumber,
  lo_custkey,
  lo_partkey,
  lo_revenue,
  lo_supplycost
FROM lineorder;

CREATE MATERIALIZED VIEW join_mv
DISTRIBUTED BY hash(lo_orderkey)
REFRESH MANUAL
AS
SELECT
  v1.c_custkey,
  v1.c_name,
  v1.c_address,
  v2.lo_orderkey,
  v2.lo_linenumber,
  v2.lo_revenue
FROM view_1 v1
JOIN view_2 v2
ON v1.c_custkey = v2.lo_custkey;

USE test_db;
SELECT
  v1.c_custkey,
  v1.c_name,
  v1.c_address,
  v2.lo_orderkey,
  v2.lo_linenumber,
  v2.lo_revenue
FROM view_1 v1
JOIN view_2 v2
ON v1.c_custkey = v2.lo_custkey;

Prinsip dasar​

Untuk mengimplementasikan penulisan ulang tampilan materialisasi berbasis teks, StarRocks membandingkan pohon sintaks abstrak kueri (atau subkuerinya) dengan pohon sintaks abstrak definisi tampilan materialisasi. Jika kedua pohon cocok, StarRocks dapat menulis ulang kueri berdasarkan tampilan materialisasi. Penulisan ulang tampilan materialisasi berbasis teks lebih sederhana dan efisien, serta memiliki lebih sedikit batasan dibandingkan dengan metode penulisan ulang kueri tampilan materialisasi tipe SPJG konvensional. Penggunaan fitur ini secara tepat dapat meningkatkan kinerja kueri secara signifikan.

Penulisan ulang tampilan materialisasi berbasis teks mendukung tidak hanya operator tipe SPJG, tetapi juga operator lain seperti Union, Window, Order, Limit, dan CTE.

Item Konfigurasi

Skenario​

Kueri yang memenuhi kondisi berikut dapat dioptimalkan ulang:

• Kueri asli konsisten dengan definisi tampilan materialisasi.

• Subquery dari kueri asli konsisten dengan definisi tampilan materialisasi.

Dibandingkan dengan penulisan ulang kueri tampilan materialisasi tipe SPJG konvensional, penulisan ulang tampilan materialisasi berbasis teks mendukung kueri yang lebih kompleks, seperti agregasi multi-lapis.

Sebagai contoh, Anda dapat membuat tampilan materialisasi berikut.

USE test_db;
CREATE MATERIALIZED VIEW mv1 REFRESH MANUAL AS
SELECT 
  user_id, 
  count(1) 
FROM (
  SELECT 
    user_id, 
    time, 
    bitmap_union(to_bitmap(tag_id)) AS a
  FROM user_tags 
  GROUP BY 
    user_id, 
    time) t
GROUP BY user_id;

Tampilan materialisasi ini dapat menulis ulang dua kueri berikut.

USE test_db;
-- SQL1
SELECT 
  user_id, 
  count(1) 
FROM (
  SELECT 
    user_id, 
    time, 
    bitmap_union(to_bitmap(tag_id)) AS a
  FROM user_tags 
  GROUP BY 
    user_id, 
    time) t
GROUP BY user_id;

-- SQL2
SELECT count(1)
FROM
（
    SELECT 
      user_id, 
      count(1) 
    FROM (
      SELECT 
        user_id, 
        time, 
        bitmap_union(to_bitmap(tag_id)) AS a
      FROM user_tags 
      GROUP BY 
        user_id, 
        time) t
    GROUP BY user_id
）m;

Namun, tampilan materialisasi ini tidak dapat menulis ulang kueri berikut yang berisi klausa ORDER BY.

USE test_db;
SELECT 
  user_id, 
  count(1) 
FROM (
  SELECT 
    user_id, 
    time, 
    bitmap_union(to_bitmap(tag_id)) AS a
  FROM user_tags 
  GROUP BY 
    user_id, 
    time) t
GROUP BY user_id
ORDER BY user_id;