Oracle 物化视图怎么用？

Ty3306 2023-08-27

1676

Oracle 物化视图

1.语法

CREATE MATERIALIZED VIEW [view_name] 
BUILD { IMMEDIATE | DEFERRED }
REFRESH [ FAST | COMPLETE | FORCE ]  
ON COMMIT|DEMAND
START WITH (start_time) 
NEXT (next_time) 
WITH { PRIMARY KEY | ROWID}
{ DISABLE | ENABLE } QUERY REWRITE
AS subquery

参数说明：
BUILD { IMMEDIATE | DEFERRED }  
# 指明创建物化视图时生不生成数据，IMMEDIATE 表示立即生成，这是默认；DEFERRED 表示创建时不生成数据，在第一次刷新时生成数据，且第一次刷新必须是完全刷新，在刷新之前物化视图不可用，因此不能指定查询重写子句；

REFRESH [FAST | COMPLETE | FORCE] 
# REFRESH 后的三个关键字 [FAST|COMPLETE|FORCE] 表示物化视图的刷新方式：FAST 表示快速增量刷新，刷新自上次刷新时间到当前时间的记录，需要物化视图日志；COMPLETE 表示完全刷新，会删除物化视图中的所有记录，按照语句重新生成；FORCE 表示满足快速刷新的条件就尽可能快速刷新，否则完全刷新；

ON COMMIT | DEMAND :  ON [COMMIT|DEMAND] 
# 表示物化视图的两种刷新模式：ON COMMIT 表示基表一旦有了事务提交，就刷新数据，更新物化视图，该类物化视图增加基表提交所需时间；ON DEMAND 表示手动刷新，可以在需要刷新数据时使用 DBMS_MVIEW.REFRESH 等方法来进行刷新；

START WITH (start_time) 
#  START WITH 指定的时间start_time表示第一次自动刷新时间

NEXT (next_time) 
# NEXT 指定的时间next_time表示下一次刷新时间，如果 NEXT 子句未指定，则只自动刷新一次；

WITH { PRIMARY KEY | ROWID } 
# 指定了创建的是主键物化视图还是 ROWID 物化视图；主键物化视图必须在查询子句中直接指定所有的主键列；ROWID 物化视图用于查询子句未全部包含基表的主键列，同时 ROWID 物化视图有一些限制，如查询子句只能是单表查询，且不能包括 Distinct、聚集函数、GROUP BY 或 CONNECT BY 子句、子查询、联接、set操作，ROWID的物化视图的基表如果重组了，那么在执行一次完全刷新之前，不能快速刷新；

{ DISABLE | ENABLE } QUERY REWRITE # { DISABLE | ENABLE } QUERY REWRITE：指定物化视图是否能够应用于查询重写。查询重写有一些限制，比如物化视图中使用到的所有用户自定义函数都必须是 DETERMINISTIC（可记住函数调用结果，对性能有好处）；语句中的表达式都是可重复的，例如不能包含 CURRENT_TIME 或 USER、序列值(例如 CURRVAL 或 NEXTVAL 伪列)或 SAMPLE 子句这种不可重复表达式；
AS SUBQUERY :

2.注意

1.on commit 模式只能在查询语句中不使用 dblink 的情况下使用，也就是只能在本地用，不能连接远程的对象。

2.创建物化视图的用户需要有 create materialized view 的权限，还必须拥有 create table 的权限，必须单独赋权，即使直接给 dba 的权限也不行。

3.fast 刷新方式必须要有 物化视图日志，否则不能使用 fast 方式。

4.如果建立脚本中没有 start with  next 字句的情况下，则不会自动刷新，可单独建立 job 来定期执行刷新动作，刷新语句：
DBMS_MVIEW.refresh('scott.a','F');

5.建立 dblink 的时候使用的连接账号最好有原库中基表的访问权限。

6.如果目标用户明明有远端表的访问权限，但在创建物化视图的时候还是提示说没有权限访问远端表，则有可能是缺少访问 MLOG$_表名的权限。

3.创建物化视图

背景：192.168.65.22 数据库中有 mv.stu 表,在 192.168.65.23 数据库中 ty 用户下建立 mv.stu 表的物化视图
步骤：
	1. 基表创建物化视图日志
	2. 远程db 创建访问基表的 dblink
	3. 远程db 创建物化视图
	4. 如有索引则在物化视图上添加索引··

3.1 创建物化视图日志

在 192.168.65.22 上创建 mv.stu 的物化视图日志
create materialized view log on mv.stu with [rowid | primary key]

ps:
# 如果基表有主键 则用 with primary key，否则用 with rowid

创建好物化视图日志后会多 2 个表：
MLOG$_表名：存放物化视图日志
RUPD$_表名：支持物化视图可更新的表

3.2 创建 DBLink

在 192.168.65.23 上创建连接到 192.168.65.22 的 DBLink
create public database link yy connect to mv identified by oracle using 'ty_primary'
ps:
    yy ：dblink 的名字
    mv ：是 22 上的用户名
    oracle ：是 22 上 mv 用户的密码
  	ty_primary ：是连接字符串，在 tnsnames.ora 中定义的连接到 192.168.65.22 DB 的。

# 测试 dblink 是否成功
select * from mv.stu@yy
# 如果有结果则表示成功

3.3 创建物化视图

在 192.168.65.23 上创建物化视图
create materialized view TY.STU 
refresh fast 
start with sysdate
next sysdate + 2/(60*24)
with rowid
as
select * from mv.stu@yy

4.查询相关

DBA_BASE_TABLE_MVIEWS ：查询物化视图最后一次刷新的时间
SQL> select * from DBA_BASE_TABLE_MVIEWS;
OWNER	   MASTER			  MVIEW_LAS   MVIEW_ID
---------- ------------------------------ --------- ----------
MV	   SS				  04-JUL-20	     4
MV	   STU				  04-JUL-20	     5

物化视图日志表：
MLOG$_A：在创建好物化视图后，会生成一个 MLOG$_ 为前缀，加表名的 物化视图日志表，字段如下：
SQL> desc MV.MLOG$_STU;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 M_ROW$$					    VARCHAR2(255)
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)

DEPARTMENT_ID：基表的主键，默认创建的物化视图日志为子句 with primar key，日志表会包含基表的主键列；
SNAPTIME$$：填充刷新时间；
DMLTYPE$$：填充DML操作的类型，I表示插入、D表示删除、U表示更新；
OLD_NEW$$：表示值的新旧，N表示新值，对应插入操作；O表示旧值，对应删除操作；U表示更新值，对应更新操作；
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段 ；
XID$$：基于current scn类型的物化视图日志会用到XID列，该列与current scn 的对应关系存储在视图ALL_SUMMAP中；

5.删除物化视图相关

5.1 删除物化表的日志表

DROP MATERIALIZED VIEW LOG ON 表名;

5.2 删除物化视图

DROP MATERIALIZED VIEW 表名；

6.不同步处理

6.1 Job 问题

如果是 job 问题导致不能同步，则按照如下方法操作。
1.copy 其他物化视图的job 脚本，如：
        BEGIN 
          SYS.DBMS_JOB.REMOVE(8);
        COMMIT;
        END;
        /
        
        DECLARE
          X NUMBER;
        BEGIN
          SYS.DBMS_JOB.SUBMIT
          ( job       => X 
           ,what      => 'dbms_refresh.refresh(''"TY"."STU"'');'
           ,next_date => to_date('18/02/2021 18:46:23','dd/mm/yyyy hh24:mi:ss')
           ,interval  => 'sysdate + 2/(60*24) '
           ,no_parse  => FALSE
          );
          SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
        COMMIT;
        END;
        /
2.修改其中的内容，如需要操作的对象；
3.执行脚本新建同步 job；
4.观察 job 执行状态及数据同步情况，可以看物化视图日志表，如果里面有数据，则表示还有数据没同步；
5.如果新建 job 有限，则删除老的 job 对象；

如果要将 job 修改到用户 schema 下，则直接修改 dba_jobs 表中的 log_user、priv_user、schema_user；
update dba_jobs set log_user='TY',priv_user='TY',schema_user='TY' where job=8;

oracle 物化视图数据库视图 fast 墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle（共15篇）

计算 Oracle 相关知识

Oracle 物化视图怎么用？

Oracle 物化视图

1.语法

2.注意

3.创建物化视图

3.1 创建物化视图日志

3.2 创建 DBLink

3.3 创建物化视图

4.查询相关

5.删除物化视图相关

5.1 删除物化表的日志表

5.2 删除物化视图

6.不同步处理

6.1 Job 问题

文章被以下合辑收录

评论