#### 物化檢視主要用途
- 提高查詢效率：通過存放多表查詢結果，提高查詢效率
- 同步表資料： 可通過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
--提交重新整理，不支援遠端， 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
-

【MVIEW】Oracle通過物化檢視同步表資料及簡介

相關文章