Oracle 物化视图详解（materialized）_越努力越幸运再努力_oracle 物化视图

未知 02-07 7215

文章目录 1 概述 1.1 思维导图 2 语法 2.1 创建2.2 查询2.3 修改2.4 删除 3 扩展 3.1 手动刷新3.2 创建物化视图日志 3.2.1 with primary key3.2.2 with rowid

1 概述

小提示：要想创建 "物化视图，至少具有 ‘CREATE MATERIALIZED VIEW’ 权限"

-- 权限查询，非 DBA 用户，则使用 user_sys_privs 即可 SELECT * FROM dba_sys_privs t WHERE t.privilege LIKE '%MATERIALIZED%'; grant create materialized view to scott; -- 授权 revoke create materialized view from scott; -- 回收 1.1 思维导图

2 语法 2.1 创建

Oracle 12c 官方文档 - 物化视图

创建物化视图时的选项有很多，知晓常用的即可:

create materialized view 物化视图名 -- 1. 创建物化视图 build [immediate | deferred] -- 2. 创建方式，默认 immediate refresh [force | fast | complete | never] -- 3. 物化视图刷新方式，默认 force on [commit | demand] -- 4. 刷新触发方式 start with 开始时间 -- 5. 设置开始时间 next 间隔时间 -- 6. 设置间隔时间 with [primary key | rowid] -- 7. 类型，默认 primary key [enable | disable] query rewrite -- 8. 是否启用查询重写 as -- 9. 关键字查询语句; -- 10. select 语句

语法解释：

1. "创建 build" 的方式 (1) 'immediate'：立即生效，默认。 (2) 'deferred' : 延迟至第一次 refresh 时才生效 2. "刷新 refresh" 的方式 (1) force ：默认。如果可以 '快速刷新' 就 '快速刷新'，否则执行 '完全刷新' (2) fast ：'快速刷新'。只刷新 '增量' 部分（前提：创建 '物化日志'） (3) complete: '完全刷新'。刷新时更新全部数据，包括视图中已经生成的原有数据 (4) never : 从不刷新 3. "触发" (请注意，on demand 中，才需要设置 '开始时间' 和 '间隔时间') -- 冲突 (1) on commit：基表有 commit 动作时，刷新刷图（"不能跨库执行"） (2) on demand：在需要时刷新 [1] 根据后面设定的 '开始时间' 和 '结束时间' 进行刷新 [2] 手动调用 dbms_mview 包中的过程进行刷新 4. 基于基表的 primary key 或 rowid 创建 (1) 如果是基于 rowid，则不能对基表执行 '分组函数'、'多表连接' 等需要把多个 rowid 合成一行的操作（理由很简单：到底以哪个 rowid 为准呢？） 5. enable query rewrite 启用查询重写（请注意， '开始时间' 和 '间隔时间' 不支持）-- 冲突 (1) 不支持的理由也很简单。所谓的 '重写'，就是讲对基表的查询定位到物化视图上，而 '开始时间' 和 '间隔时间' 会造成物化视图上部分数据延迟，所以，不能重写 (2) 参数: query_rewrite_enabled (可通过 v$parameter 视图查询)

示例：每 3 分钟，同步一次基表数据（用户 scott） 1. 先创建基表 person_info，并插入几条数据

CREATE TABLE person_info ( person_no VARCHAR2(10), NAME VARCHAR2(30), create_date DATE ); INSERT INTO person_info(person_no, NAME, create_date) VALUES('001', '瑶瑶', SYSDATE); INSERT INTO person_info(person_no, NAME, create_date) VALUES('002', '倩倩', SYSDATE); COMMIT;

2. 创建物化视图：每 3 分钟刷新一次

CREATE MATERIALIZED VIEW mvw_person_info BUILD IMMEDIATE REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT SYSDATE + 3/1440 AS SELECT pi.person_no, pi.name, pi.create_date FROM person_info pi;

测试语句：先查询，等个十几秒在执行 insert，再等 3 分钟左右，观察前后数据

SELECT t.* FROM mvw_person_info t; INSERT INTO person_info(person_no, NAME, create_date) VALUES('003', '优优', SYSDATE); COMMIT;

测试截图：

2.2 查询 1. 查询物化视图，非 DBA 用户，请查询 all_mviews 或 user_mviews SELECT * FROM dba_mviews t WHERE t.owner = 'SCOTT' AND t.mview_name = 'MVW_PERSON_INFO'; 2. 查询一般视图 SELECT * FROM dba_views; 2.3 修改 alter materialized view 物化视图名 refresh [force | fast | complete | never] on [commit | demand] start with 开始时间 next 间隔时间 2.4 删除 drop materialized view 物化视图名; 3 扩展 3.1 手动刷新 BEGIN dbms_mview.refresh(list => '视图名', method => 'fast', -- 增量刷新 refresh_after_errors => TRUE); END; 3.2 创建物化视图日志 1. 适用于 'fast' 增量刷新 2. with primary key 3. with rowid 3.2.1 with primary key

(1) 首先创建一张表 student_info ：

CREATE TABLE student_info ( student_no VARCHAR2(10), NAME VARCHAR2(30) ); ALTER TABLE student_info ADD CONSTRAINT pk_student_info_student_no PRIMARY KEY(student_no);

(2) 然后创建物化视图日志，指定 primary key：

create materialized view log on student_info with primary key [including new values]; -- including new values 允许 Oracle 将数据库新、旧值都保存在物化视图日志中 -- 即 update 前和 update 后都保存，按需设置即可

(3) 然后在基表 student_info 中插入并更新一条记录：

INSERT INTO student_info(student_no, NAME) VALUES('001', '小优子'); UPDATE student_info t SET t.name = '小游子' WHERE t.student_no = '001'; COMMIT;

(4) 最后查询物化视图日志信息：

SELECT * FROM all_mview_logs; SELECT * FROM mlog$_student_info; 3.2.2 with rowid 创建 'fast' 增量模式的物化视图条件： (1) select 语句中包含到的每一个表都需要创建 '物化日志' (2) select 中必须包含涉及到所有表的 'rowid' (3) select 中必须明确具体的列，不允许使用 '*'

(1) 首先创建两张表：

CREATE TABLE test_a ( a_id VARCHAR(10), NAME VARCHAR2(30) ); ALTER TABLE test_a ADD CONSTRAINT pk_test_a_a_id PRIMARY KEY(a_id); CREATE TABLE test_b ( b_id VARCHAR(10), NAME VARCHAR2(30) ); ALTER TABLE test_b ADD CONSTRAINT pk_test_b_b_id PRIMARY KEY(b_id);

(2) 对所有基表创建物化视图日志：

create materialized view log on test_a with rowid including new values; create materialized view log on test_b with rowid including new values;

(3) 创建 fast 增量模式的物化视图：

CREATE MATERIALIZED VIEW mvw_test_ab REFRESH FAST WITH ROWID ON DEMAND START WITH SYSDATE NEXT SYSDATE + 3/1440 AS SELECT t1.a_id, t1.name a_name, t1.rowid a_rowid, t2.b_id, t2.name b_name, t2.rowid b_rowid FROM test_a t1, test_b t2 WHERE t1.a_id = t2.b_id;

(4) 查询（此时没数据）：

SELECT * FROM mvw_test_ab; SELECT * FROM all_mview_logs; SELECT * FROM mlog$_test_a; SELECT * FROM mlog$_test_b;

(5) 插入测试数据：

INSERT INTO test_a(a_id, NAME) VALUES('1', 'a1'); INSERT INTO test_a(a_id, NAME) VALUES('2', 'a2'); INSERT INTO test_a(a_id, NAME) VALUES('3', 'a3'); INSERT INTO test_b(b_id, NAME) VALUES('1', 'b1'); INSERT INTO test_b(b_id, NAME) VALUES('2', 'b2'); INSERT INTO test_b(b_id, NAME) VALUES('3', 'b3'); COMMIT;

(6) 再查询（此时有数据了）：