物化视图存储基于表的快照数据。通常情况下,物化视图被称为主表(在复制期间),明细表(在数据仓库中)
创建的物化视图通常情况下主键,rowid,和子查询视图。
第一种:
对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。
SQL> grant create materialized view to scott;
SQL>conn soctt/tiger
SQL> create materialized view mv_emp_norefresh as select * from emp;
SQL> create materialized view mv_emp_norefresh2 as select empno,ename,sal,comm, deptno from emp;
SQL> create materialized view mv_emp_norefresh3 as select empno,ename,sal,comm,nvl(comm,0)+sal as total_incoming, deptno from emp;
SQL> create materialized view mv_emp_loc as select ename,loc from emp,dept where emp.deptno=dept.deptno;
创建物化视图
CREATE MATERIALIZED VIEW FB_CORE.MV_STATUS_CHANGE_INFORMATION
REFRESH FORCE ON DEMAND
WITH ROWID
NEXT NULL
ENABLE QUERY REWRITE
AS
SELECT status_change_information.* FROM risk_uw_user.status_change_information@RPTDB01 status_change_information;
删除物化视图
drop materialized view mv_table1;
删除物化视图日志
drop materialized view log on table1;
=============================================================================
create materialized view XX_MV_SYNC_PTS_WIP
build immediate
refresh force on demand
start with sysdate next sysdate+1
with primary key
as
select 语句!
可选参数说明
build:创建MV时是否立即刷新
build immediate(默认):创建立即刷新
build deferred:延迟刷新,刚创建不会刷新
refresh:刷新方式
fast:快速刷新,需要创建MV Log,适合简单查询
complete:完全刷新
force(默认):自己判断进行fast还是complete
on:刷新模式
ON demand(默认):手动刷新,可定义刷新频率进行自动刷新
ON commit:主表commit就自动刷新