マテリアライズドビューを作成してリフレッシュ

Oracle

-- FAST refresh first requires an MV log on the source table:
CREATE MATERIALIZED VIEW LOG ON sales
  WITH ROWID, SEQUENCE (region, amount) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_revenue
BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT region, SUM(amount) total, COUNT(*) cnt, COUNT(amount) amount_cnt
   FROM sales GROUP BY region;

-- Manual refresh
EXEC DBMS_MVIEW.REFRESH('MV_REVENUE', 'C');

REFRESH FAST にはマテリアライズドビューログが必要。COMPLETE('C')は完全に再計算し、FAST('F')は増分。ON COMMIT/ON DEMAND でリフレッシュのタイミングが決まる。

PostgreSQL

CREATE MATERIALIZED VIEW mv_revenue AS
SELECT region, SUM(amount) AS total FROM sales GROUP BY region;

-- Refresh (CONCURRENTLY requires a unique index)
CREATE UNIQUE INDEX ON mv_revenue (region);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_revenue;

REFRESH はすべてを再計算する(増分ではない)。CONCURRENTLY は読み取りをブロックしないが、一意インデックスが必要で低速。

MySQL

-- MySQL/MariaDB has no built-in materialized views.
-- Approach: summary table + refresh with a trigger or a scheduled event
CREATE TABLE mv_revenue (region VARCHAR(50) PRIMARY KEY, total DECIMAL(18,2));

CREATE EVENT ev_mv_revenue ON SCHEDULE EVERY 1 HOUR DO
  REPLACE INTO mv_revenue SELECT region, SUM(amount) FROM sales GROUP BY region;

ネイティブのマテリアライズドビューはサポートされない。サマリーテーブルを保持し、トリガーで即時に、または EVENT SCHEDULER で定期的に再投入する。

SQL Server

-- Indexed view (materialized view equivalent)
CREATE VIEW dbo.mv_revenue WITH SCHEMABINDING AS
SELECT region, SUM(ISNULL(amount, 0)) AS total, COUNT_BIG(*) AS cnt
FROM dbo.sales GROUP BY region;
GO
CREATE UNIQUE CLUSTERED INDEX ix_mv_revenue ON dbo.mv_revenue (region);

SQL Server ではインデックス付きビューを使用する。WITH SCHEMABINDING が必須で、一意のクラスタ化インデックスを作成するとデータが物理的に格納され自動的に維持される(別途 REFRESH は不要)。GROUP BY では COUNT_BIG(*) が必要。SUM 内の列が NULL 許容なら SUM(ISNULL(amount, 0)) と書く(そうしないとエラー 8662)。ビューは QUOTED_IDENTIFIER と ANSI_NULLS を ON にして作成する必要がある。クエリオプティマイザによる自動ビューマッチングは Enterprise(および Developer)エディションのみ。Standard では WITH (NOEXPAND) ヒントでビューを参照する。

MongoDB

// On-demand materialized view: write the result to a collection with $merge
db.sales.aggregate([
  { $group: { _id: "$region", total: { $sum: "$amount" } } },
  { $merge: { into: "mv_revenue", whenMatched: "replace", whenNotMatched: "insert" } }
]);

集計結果を $out または $merge で永続コレクションに書き込むことで、オンデマンドのマテリアライズドビューが得られる。リフレッシュするにはパイプラインを再実行する(手動またはスケジューラで)。

Redis

# Not applicable: Redis has no materialized views. The computed result is written to a key
# and cached with a TTL.
SET mv:revenue:region1 12345 EX 3600

マテリアライズドビューの概念はない。最も近い方法は、事前計算した値をキー(String/Hash/Sorted Set)に保持し、EX/TTL で期間限定でキャッシュすること。

Elasticsearch

# Continuous/materialized summary index with a transform
PUT _transform/mv_revenue
{ "source": { "index": "sales" },
  "dest": { "index": "mv_revenue" },
  "pivot": { "group_by": { "region": { "terms": { "field": "region" } } },
            "aggregations": { "total": { "sum": { "field": "amount" } } } } }
POST _transform/mv_revenue/_start

transform はソースインデックスをピボットし、サマリー用の宛先インデックスに実体化する。この例はバッチ transform で、_start で一度実行されて停止する。新しいデータの到着に応じて自動更新される continuous transform にするには、時刻フィールドを指定した "sync": { "time": { "field": "<time_field>", "delay": "60s" } } を構成に追加する(continuous: true という設定はない)。

DBAtlas で開く →