创建并刷新物化视图

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 中的列可为空，应写成 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 运行一次后停止。若需要随新数据到达自动更新的连续 transform，在配置中添加带时间字段的 "sync": { "time": { "field": "<time_field>", "delay": "60s" } }（不存在 continuous: true 设置）。

在 DBAtlas 中打开 →