视图与物化视图

Oracle：视图与物化视图

第一章 视图

1.1 什么是视图

视图（View）是一种数据库对象，它是从一个或多个数据表（或已有视图）中导出的虚拟表。视图本身并不存储数据，其数据来源于引用的基表，视图的结构和内容是对基表查询的结果。换句话说，视图存储的是一条查询语句的定义，每次引用视图时会动态执行该语句生成结果。

视图的优点

1. 简化数据操作：通过视图，用户可以用更简单的方式处理复杂数据。
2. 聚焦特定数据：隐藏不必要或敏感的数据，展示特定内容。
3. 安全机制：为不同用户定制访问权限，提升数据安全性。
4. 向后兼容性：当基表结构变更时，视图可作为兼容接口，保持现有应用正常运行。

---

1.2 创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name
AS 
subquery
[WITH CHECK OPTION]
[WITH READ ONLY]

选项解释

• OR REPLACE：如果视图已存在，自动重建视图。
• FORCE：即使基表不存在，也强制创建视图（后续基表创建后可用）。
• subquery：定义视图的SELECT查询语句，可包含列别名。
• WITH CHECK OPTION：确保插入或修改的数据行满足视图定义的条件。
• WITH READ ONLY：视图只读，禁止执行任何DML操作（如INSERT、UPDATE、DELETE）。

---

1.3 删除视图语法

DROP VIEW view_name;

简单直接，用于删除指定视图。

---

1.4 视图案例

1.4.1 简单视图的创建与使用

什么是简单视图？
简单视图是指基于单表查询且不包含聚合函数的视图。它是最基础的视图类型，支持查询和DML操作。

需求：创建业主类型为1的业主信息视图。

CREATE OR REPLACE VIEW view_owners1 AS
SELECT * FROM t_owners WHERE ownertypeid = 1;

查询视图：

SELECT * FROM view_owners1 WHERE addressid = 1;

更新视图：

UPDATE view_owners1 SET name = '王刚' WHERE id = 2;
SELECT * FROM view_owners1; -- 查看更新后的视图
SELECT * FROM t_owners;     -- 验证基表数据也同步更新

结论：
视图是虚拟表，其数据来源于基表。修改视图会影响基表，反之亦然。视图存储的不是数据，而是一条SQL查询语句。

练习：

-- 创建视图：查询区域类型为2的地址信息
CREATE OR REPLACE VIEW view_addr AS
SELECT * FROM t_address WHERE areaid = 2;

-- 查询区域类型为1的地址信息（结果为空，因视图定义限制）
SELECT * FROM view_addr WHERE areaid = 1;

-- 修改存在的列值
UPDATE view_addr SET name = '霍营地铁站' WHERE id = 5;
COMMIT;

-- 修改不存在的记录
UPDATE view_addr SET name = '流星花园' WHERE id = 1; -- 不会报错，但无影响
COMMIT;

-- 验证
SELECT * FROM view_addr;
SELECT * FROM t_address;

---

1.4.2 带检查约束的视图

需求：创建区域ID为2的地址视图，并添加检查约束。

CREATE OR REPLACE VIEW view_address2 AS
SELECT * FROM t_address WHERE areaid = 2
WITH CHECK OPTION;

测试更新：

UPDATE view_address2 SET areaid = 1 WHERE id = 4; -- 报错，因违反约束

解释：
WITH CHECK OPTION确保视图的DML操作不会使数据超出视图定义的条件（如areaid = 2）。

练习：

-- 创建视图：区域类型为3，带检查约束
CREATE OR REPLACE VIEW view_addr_2 AS
SELECT * FROM t_address WHERE areaid = 3
WITH CHECK OPTION;

-- 查询
SELECT * FROM view_addr_2;

-- 修改name（成功）
UPDATE view_addr_2 SET name = '霍营地铁站' WHERE id = 7;
COMMIT;

-- 修改areaid（报错）
UPDATE view_addr_2 SET areaid = 2 WHERE id = 3; -- 违反约束

---

1.4.3 只读视图

需求：创建只读视图，禁止修改。

CREATE OR REPLACE VIEW view_owners1 AS
SELECT * FROM t_owners WHERE ownertypeid = 1
WITH READ ONLY;

测试更新：

UPDATE view_owners1 SET name = '和珅' WHERE id = 2; -- 报错，因只读

练习：

-- 创建只读视图：区域类型为3
CREATE OR REPLACE VIEW view_addr_3 AS
SELECT * FROM t_address WHERE areaid = 3
WITH READ ONLY;

-- 查询
SELECT * FROM view_addr_3;

-- 修改（报错）
UPDATE view_addr_3 SET name = '西三期' WHERE id = 7; -- 失败

---

1.4.4 带错误的视图

场景：基表不存在时创建视图。

CREATE OR REPLACE VIEW view_temp AS
SELECT * FROM t_temp; -- 报错，表不存在

使用FORCE选项：

CREATE OR REPLACE FORCE VIEW view_temp AS
SELECT * FROM t_temp; -- 创建成功，后续表存在时可用

说明：
FORCE选项允许在基表缺失的情况下创建视图，适用于表尚未创建但未来会出现的场景。

---

1.4.5 复杂视图

什么是复杂视图？
复杂视图包含聚合函数或多表关联查询，通常不可更新。

多表关联查询

需求：查询业主编号、名称及类型名称。

CREATE OR REPLACE VIEW view_owners AS
SELECT o.id 业主编号, o.name 业主名称, ot.name 业主类型
FROM t_owners o, t_ownertype ot
WHERE o.ownertypeid = ot.id;

查询与更新：

SELECT * FROM view_owners;
UPDATE view_owners SET 业主名称 = '范小冰' WHERE 业主编号 = 1; -- 成功
UPDATE view_owners SET 业主类型 = '普通居民' WHERE 业主编号 = 1; -- 成功

分组聚合查询

需求：按年月统计水费金额。

CREATE VIEW view_accountsum AS
SELECT year, month, SUM(money) moneysum
FROM t_account
GROUP BY year, month
ORDER BY year, month;

测试更新：

UPDATE view_accountsum SET year = '2023' WHERE month = '01'; -- 失败，因聚合视图不可更新

练习：

-- 创建视图：地址ID、名称、区域名称
CREATE OR REPLACE VIEW view_addr_area AS
SELECT t1.id, t1.name ad_name, t2.name ar_name
FROM t_address t1, t_area t2
WHERE t1.areaid = t2.id;

-- 查询
SELECT * FROM view_addr_area;

-- 修改
UPDATE view_addr_area SET ad_name = '流行花园' WHERE id = 1;
UPDATE view_addr_area SET ar_name = '海淀区' WHERE id = 1;

-- 创建聚合视图：每个区域的地址数量
CREATE OR REPLACE VIEW view_addr_2 AS
SELECT t1.areaid, (SELECT name FROM t_area t2 WHERE t1.areaid = t2.id) area_name, COUNT(1) cnt
FROM t_address t1
GROUP BY t1.areaid;

-- 查询
SELECT * FROM view_addr_2;

-- 修改（失败）
UPDATE view_addr_2 SET area_name = '海淀' WHERE areaid = 1; -- 报错
UPDATE view_addr_2 SET cnt = 666 WHERE areaid = 1; -- 报错

---

第二章 物化视图

2.1 什么是物化视图

物化视图（Materialized View）是将查询结果集存储为实体化对象的视图。与普通视图不同，物化视图占用存储空间，类似于一张表。它通过保存数据副本提高查询效率，避免每次访问都重新执行查询。

与普通视图的区别

• 普通视图：虚拟表，仅存储查询语句，动态生成结果。
• 物化视图：实体表，存储实际数据，查询效率更高。

---

2.2 创建物化视图语法

CREATE MATERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED]
REFRESH [FAST | COMPLETE | FORCE]
[ON [COMMIT | DEMAND] | START WITH (start_time) NEXT (next_time)]
AS
subquery

选项解释

• BUILD IMMEDIATE：创建时立即生成数据（默认）。
• BUILD DEFERRED：创建时不生成数据，后续手动生成。
• REFRESH：刷新方式：
  • FAST：增量刷新，仅更新修改部分。
  • COMPLETE：完全刷新，重建整个视图。
  • FORCE：Oracle自动选择刷新方式（默认）。
• ON COMMIT：基表提交时自动刷新。
• ON DEMAND：手动刷新（默认）。

---

2.3 物化视图案例

2.3.1 手动刷新的物化视图

需求：查询地址ID、名称和区域名称。

CREATE MATERIALIZED VIEW mv_address AS
SELECT ad.id, ad.name adname, ar.name ar_name
FROM t_address ad, t_area ar
WHERE ad.areaid = ar.id;

查询：

SELECT * FROM mv_address;

插入数据后：

INSERT INTO t_address VALUES (8, '宏福苑小区', 1, 1);
SELECT * FROM mv_address; -- 新数据未出现

-- 手动刷新
BEGIN
  DBMS_MVIEW.REFRESH('mv_address', 'C'); -- 'C'表示完全刷新
END;

---

2.3.2 自动刷新的物化视图

需求：基表变化时，物化视图自动更新。

CREATE MATERIALIZED VIEW mv_address2
REFRESH ON COMMIT
AS
SELECT ad.id, ad.name adname, ar.name ar_name
FROM t_address ad, t_area ar
WHERE ad.areaid = ar.id;

说明：
插入或修改基表数据并提交后，物化视图会自动同步。

---

2.3.3 创建时不生成数据的物化视图

CREATE MATERIALIZED VIEW mv_address3
BUILD DEFERRED
REFRESH ON COMMIT
AS
SELECT ad.id, ad.name adname, ar.name ar_name
FROM t_address ad, t_area ar
WHERE ad.areaid = ar.id;

查询与生成数据：

SELECT * FROM mv_address3; -- 初始无数据

-- 手动生成数据
BEGIN
  DBMS_MVIEW.REFRESH('mv_address3', 'C');
END;

---

2.3.4 增量刷新的物化视图

前提：创建物化视图日志。

CREATE MATERIALIZED VIEW LOG ON t_address WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON t_area WITH ROWID;

创建物化视图：

CREATE MATERIALIZED VIEW mv_address4
REFRESH FAST
AS
SELECT ad.rowid adrowid, ar.rowid arrowid, ad.id, ad.name adname, ar.name ar_name
FROM t_address ad, t_area ar
WHERE ad.areaid = ar.id;

插入数据后刷新：

INSERT INTO t_address VALUES (11, '天通苑北4区', 4, 5);
BEGIN
  DBMS_MVIEW.REFRESH('mv_address4', 'C');
END;

注意：
增量刷新需要物化视图日志支持，且查询中必须包含基表的ROWID。

---

2.3.5 全量刷新的物化视图

需求：统计每个区域的地址数量。

CREATE MATERIALIZED VIEW mv_addr_7
REFRESH ON COMMIT
AS
SELECT areaid, COUNT(1) AS cnt
FROM t_address
GROUP BY areaid;

测试：

INSERT INTO t_address VALUES (11, '天通苑5区', 2, 1);
COMMIT;
SELECT * FROM mv_addr_7; -- 自动更新

---

总结

视图和物化视图是Oracle数据库中非常实用的工具：

• 视图：虚拟表，简化操作、增强安全性，适合动态查询。
• 物化视图：实体表，提高查询效率，适合频繁访问的场景。