【MVIEW】Oracle物化视图同步表数据及简介

来源：这里教程网时间：2026-03-03 16:46:51 作者：

#### 物化视图主要用途
- 提高查询效率：通过存放多表查询结果，提高查询效率
- 同步表数据： 可通过dblink方式，增量定时同步表数据

#### 远程增量同步数据
生产端环境
``` sql
--生成测试表
create table t2021 (id int primary key,name varchar2(30));
--批量插入数据
declare
i_int int;
begin
 for i in 1 .. 100 loop
 select nvl(max(id)+1,1) into i_int from t2021; 
 insert into t2021 values(i_int,'aa'||i_int);
  end loop;
 commit;
 end;
/
--创建物化视图日志
create materialized view log on t2021 with primary key;
--查看物化视图日志信息
col log_owner for a15
col master for a20
col log_table for a25
col last_purge_date for a20
select log_owner,master,log_table,rowids,primary_key,
to_char(last_purge_date,'yyyy/mm/dd hh24:mi:ss')  last_purge_date
from dba_mview_logs where master='T2021';
--删除物化视图日志
DROP MATERIALIZED VIEW LOG ON  [log_table];
```
目标端环境
``` sql
--创建物化视图
create materialized view t2021 BUILD IMMEDIATE
REFRESH FAST WITH PRIMARY KEY on DEMAND as select * from t2021@mytest116;
--手动刷新
exec DBMS_MVIEW.REFRESH('T2021','F');  
--创建定时刷新的物化视图
create materialized view t2021 BUILD IMMEDIATE  REFRESH FAST WITH PRIMARY KEY on DEMAND
START WITH to_date('2021/07/09 9:32:00', 'yyyy/mm/dd hh24:mi:ss')
--每两分钟刷新一次 or TRUNC(SYSDATE,'dd')+1+2/24 --每天两点
NEXT TRUNC(SYSDATE,'mi')+2/24/60
as select * from t2021@mytest116;
--查看物化视图信息
col mview_name for a20
col owner for a15
col master_link for a15
col LAST_REFRESH_DATE  for a20
SELECT owner,mview_name,updatable,master_link,
to_char(LAST_REFRESH_DATE,'yyyy/mm/dd hh24:mi:ss') LAST_REFRESH_DATE
,REFRESH_MODE,REFRESH_METHOD,REWRITE_CAPABILITY
FROM dba_mviews WHERE mview_name='T2021';
--查看物化视图定时刷新信息
col rowner for a15
col rname for a15
col INTERVAL for a35
col job_name for a20
select rowner,rname,IMPLICIT_DESTROY,JOB,NEXT_DATE,INTERVAL,job_name 
from dba_refresh where rowner='MYTEST';
--删除物化视图
DROP MATERIALIZED VIEW t2021; 
```
#### 提高查询效率
``` sql
--查询语句
select e.ename,d.dname from dept d ,emp e where d.deptno=e.deptno;
--创建物化视图表
CREATE MATERIALIZED VIEW LOG on dept WITH rowid,SEQUENCE (deptno,dname) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG on emp WITH rowid,SEQUENCE (deptno,ename) INCLUDING NEW VALUES;
--创建物化视图 查询重写方式 ,刷新方式(complete:完整；fast:增量，force:自动判断)
create Materialized view emp_dept
REFRESH complete ON COMMIT
enable query rewrite
as
select e.ename,d.dname from dept d,emp e where d.deptno=e.deptno;
--聚集语句支持增量，如下
create Materialized view emp_dept
REFRESH fast WITH rowid ON COMMIT
enable query rewrite
as
select d.dname,count(*) from dept d,emp e where d.deptno=e.deptno group by d.dname;
```
> 查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据
含聚集的物化视图快速刷新注意事项：
- 1.必须满足所有快速刷新物化视图都满足的条件；
- 2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
  (1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
  (2)必须指明ROWID和INCLUDING NEW VALUES；
  (3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
- 3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
- 4.必须指定COUNT；
- 5.SELECT列表中必须包括所有的GROUP BY列；
#### 提交刷新方式
```sql
--提交刷新，不支持远程， on prebuilt table  需要创建相同的表名，用于存放基础数据
create table t2021_new as select * from t2021;
create materialized view t2021_new  on prebuilt table refresh fast on commit as select * from t2021;
```
> "on prebuilt table"的好处：如果更新物化视图，同名表也会被更新。当删除物化视图，不会删除同名的表，且保留从基表更新过来的数据
#### 参考
- https://blog.csdn.net/tianlesoftware/article/details/4713553
- https://blog.csdn.net/demonson/article/details/81451985