マテリアライズドビューによるクエリの高速化

-- 販売データテーブル
CREATE TABLE sales (
  order_id INT primary key,
  user_id INT,
  item_id INT,
  item_count INT,
  region VARCHAR(100)
);

-- 商品情報テーブル
CREATE TABLE items (
  order_id INT,
  product_id INT,
  quantity INT,
  price_per_item DECIMAL(10, 2) NOT NULL,
  pic_url varchar(1000),
  primary key (order_id, product_id)
);

-- 製品と地域ごとに販売量を集計したマテリアライズドビューmv_sales_summary
CREATE MATERIALIZED VIEW mv_sales_summary(PRIMARY KEY(item_id))
REFRESH COMPLETE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT item_id, region, SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;

-- マテリアライズドビューのログを有効化
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

-- 増分更新マテリアライズドビューを作成
CREATE MATERIALIZED VIEW mv_sales_summary_fast
REFRESH FAST
START WITH sysdate() NEXT sysdate() + interval 1 hour
AS
SELECT item_id, region, SUM(item_count) AS total_count, count(*)  as c, count(item_count) as count
FROM sales GROUP BY item_id, region;

-- マテリアライズドビューが依存するすべてのベーステーブルに対してマテリアライズドビューのログを作成
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (user_id, item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item,pic_url) including new values;

CREATE MATERIALIZED VIEW mv_sales_items_join
PARTITION BY HASH(order_id) PARTITIONS 10
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT
    s.order_id AS order_id,
    s.user_id AS customer_id,
    s.item_id AS item_id,
    s.item_count AS quantity,
    s.region AS region,
    i.order_id AS i_id,
    i.product_id AS i_item_id,
    i.price_per_item AS price_per_item,
    i.pic_url AS pic_url
FROM
    sales s
JOIN
    items i ON s.order_id = i.order_id;

-- マテリアライズドビューが依存するすべてのベーステーブルに対してマテリアライズドビューのログを作成
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item) including new values;

CREATE MATERIALIZED VIEW mv_sales_item_join_group
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT
    s.item_id AS item_id,
    s.region AS region,
    SUM(s.item_count * i.price_per_item) AS sum_price,
    count(*) AS c,
    count(s.item_count * i.price_per_item) AS count
FROM
    sales s
JOIN
    items i ON s.order_id = i.order_id
GROUP BY item_id, region;

-- マテリアライズドビューのログを有効にする
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW mv_sales_summary_com
REFRESH FORCE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
ENABLE ON QUERY COMPUTATION
AS
SELECT item_id, region, SUM(item_count) AS total_count, count(*) as c, count(item_count) as count
FROM sales
GROUP BY item_id, region;

CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (user_id, item_id, item_count, region) including new values;
CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item,pic_url) including new values;

CREATE MATERIALIZED VIEW mv1_sales_items_join
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT
    s.order_id AS order_id,
    s.user_id AS customer_id,
    s.item_id AS item_id,
    s.item_count AS quantity,
    s.region AS region,
    i.order_id AS i_id,
    i.product_id AS i_item_id,
    i.price_per_item AS price_per_item,
    i.pic_url
FROM
    sales s
JOIN
    items i ON s.order_id = i.order_id;

-- mv1_sales_items_joinのマテリアライズドビューのログを作成
CREATE MATERIALIZED VIEW LOG ON mv1_sales_items_join WITH primary key (region,quantity,price_per_item) including new values;

-- mv1_sales_items_joinに基づいてマテリアライズドビューを作成
CREATE MATERIALIZED VIEW mv2_join_sum
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT
    region,
    sum(quantity * price_per_item) AS sum_price,
    count(*) as c,
    count(quantity * price_per_item) as count
FROM
    wide_sales_mv1
group by region;

CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_count, region) including new values;
CREATE MATERIALIZED VIEW LOG ON sales_items WITH primary key (price_per_item,pic_url) including new values;

-- カラムストアマテリアライズドビューを作成する
CREATE MATERIALIZED VIEW wide_sales_column WITH COLUMN GROUP(each column)
REFRESH FAST
START WITH sysdate()
NEXT sysdate() + interval 1 hour
AS
SELECT
    s.order_id AS order_id,
    s.user_id AS customer_id,
    s.item_id AS item_id,
    s.item_count AS quantity,
    s.region AS region,
    i.order_id AS i_id,
    i.product_id AS i_item_id,
    i.price_per_item AS price_per_item,
    i.pic_url
FROM
    sales s
JOIN
    items i ON s.order_id = i.order_id;

CREATE MATERIALIZED VIEW mv_sales_summary_select
REFRESH COMPLETE
START WITH sysdate()
NEXT sysdate() + interval 1 hour
ENABLE QUERY REWRITE
AS
SELECT item_id, region, SUM(item_count) AS total_count
FROM sales
GROUP BY item_id, region;

-- マテリアライズドビューのログを有効にする
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW mv_sales_summary_com_select
REFRESH FAST
START WITH sysdate() NEXT sysdate() + interval 1 hour
ENABLE ON QUERY COMPUTATION
ENABLE QUERY REWRITE
AS
SELECT item_id, region, SUM(item_count) AS total_sales, count(*)  as c, count(item_count) as count
FROM sales
GROUP BY item_id, region;

-- マテリアライズドビューにインデックスを作成する
CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);

CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1','c');

CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1','c','refresh_parallel => 8');

select OWNER,MVIEW_NAME,CONTAINER_NAME,QUERY,QUERY_LEN,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE,LAST_REFRESH_END_TIME from oceanbase.DBA_MVIEWS;

select JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,LAST_START_DATE,NEXT_RUN_DATE from OceanBase.DBA_SCHEDULER_JOBS where JOB_ACTION like "%mv4%";

select * from oceanbase.DBA_MVREF_RUN_STATS where MVIEWS='mv4';

-- マテリアライズドビューによるクエリのリライトを有効にする（デフォルトは無効）
SET  query_rewrite_enabled= true;

EXPLAIN SELECT item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

-- 非リアルタイムマテリアライズドビューを使用したクエリのリライトを許可する
SET query_rewrite_integrity = 'stale_tolerated';

EXPLAIN SELECT item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

-- マテリアライズドビューmv2_tbl2を強制的に指定してクエリをリライト
EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON sales_items;

DROP MATERIALIZED VIEW mv_sales_summary;