-- 数据测试 Oracle物化视图测试
--select * from datatest
--drop table datatest
--drop sequence seq_datatest
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code101','名称101',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code102','名称102',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code103','名称103',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code104','名称104',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code105','名称105',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code106','名称106',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code107','名称107',1,100);
insert into datatest (strcode,strName,isEnable,curAmount) values ('Code108','名称108',1,100);
update datatest set strcode='Code106_02' where strCode='Code106_01';
create materialized view mv_datatest Build immediate Refresh force On commit With rowid as select rowid,sysdate csts,datatest.* from datatest
GRANT SELECT,INSERT,UPDATE,DELETE ON datatest TO testuser; #常见CRUD权限
GRANT CREATE ANY MATERIALIZED VIEW TO testuser;
GRANT DROP ANY MATERIALIZED VIEW TO testuser;
-- Create table
create table datatest
(
datatestid NUMBER not null,
strcode VARCHAR2(50) not null,
strName VARCHAR2(50) not null,
isEnable NUMBER(1) not null,
curAmount NUMBER(18,2) not null,
CREATEDATE DATE default sysdate,
REMARK VARCHAR2(500)
);
-- Add comments to the columns
comment on column datatest.datatestid is '测试数据ID';
comment on column datatest.strcode is '编码';
comment on column datatest.strName is '名称';
comment on column datatest.isEnable is '启用';
comment on column datatest.curAmount is '金额';
comment on column datatest.CREATEDATE is '创建日期';
comment on column datatest.REMARK is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table datatest add constraint PK_datatest primary key (datatestid);
--create sequence
create sequence seq_datatest
minvalue 1 maxvalue 999999999999999999999999999
start with 1 increment by 1 cache 20;
--create trigger
CREATE OR REPLACE TRIGGER tri_datatest_bi
BEFORE INSERT ON datatest FOR EACH ROW
BEGIN
IF (:new.datatestid is null) or (:new.datatestid<=0) THEN
SELECT seq_datatest.nextval INTO :new.datatestid FROM DUAL;
END IF;
END;
/*
物化视图(material view)是什么?
物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。
物化视图存储基于远程表的数据,也可以称为快照(类似于MSSQL Server中的snapshot,静态快照)。对于复制,物化视图允许你在本地维护远程数据的副本,这些副本是只读的。
如果你想修改本地副本,必须用高级复制的功能。当你想从一个表或视图中抽取数据时,你可以用从物化视图中抽取。
对于数据仓库,创建的物化视图通常情况下是聚合视图,单一表聚合视图和连接视图。(这个是基于本地的基表或者视图的聚合)。
物化视图,说白了,就是物理表,只不过这张表通过oracle的内部机制可以定期更新,将一些大的耗时的表连接用物化视图实现,会提高查询的效率。当然要打开查询重写选项;
Material View的主要作用
1. 实现两个数据库之间的数据同步,可以存在时间差。
2. 如果是远程链接数据库的场景时,提高查询速度。
(由于查询逻辑复杂,数据量比较大,导致每次查询视图的时候,查询速度慢,效率低下)
物化视图的刷新方式和方法
1. 刷新的方式
Fast
Complete
Fource
2. 刷新的方法
DBMS_REFRESH.Refresh
DBMS_MVIEW.Refresh
EXEC DBMS_MVIEW.refresh('BXJ_OBJECTS_MV_T1','C');
EXEC DBMS_REFRESH.refresh('REP_MVIEWGROUP');
物化视图的刷新方式和方法
(1).在源数据库建立mview log日志文件
create materialized view log on w_1 ;
----注:(TEST为表名或者视图名,关于视图上建立物化视图,见基于视图的物化视图
----创建物化视图语句:
(2).在统计数据建立materializad view 语法
Create materialized view MV_TEST
----MVTEST为物化视图名
Build immediate
----创建时生成数据对应的是build deferred
Refresh fast
----增量刷新
On commit
----在基表有更新时提交,这里该句对视图无效
With rowid
----这里创建基于rowid的物化视图,对应的是 primary key
As
Select * from TEST;
----生成物化视图数据语句
或者
CREATE MATERIALIZED VIEW MV_TableName
BUILD IMMEDIATE --创建时立即刷新
REFRESH FORCE --如果可以快速刷新则进行快速刷新,否则完全刷新
ON DEMAND --刷新方式
START WITH SYSDATE --第一次刷新时间
NEXT SYSDATE+1/12 --刷新时间间隔
AS SELECT 1 id ‘A’ name FROM dual;
(3).调用时进行刷新
dbms_refresh.refresh('W_1')
创建物化视图
CREATE MATERIALIZED VIEW bxj_objects_mv_t1 REFRESH FAST AS SELECT * FROM apps.bxj_objects_t1;
CREATE MATERIALIZED VIEW bxj_objects_mv_t2 REFRESH FORCE AS SELECT * FROM bxj_objects_t2;
CREATE MATERIALIZED VIEW bxj_objects_mv_t3 REFRESH COMPLETE AS SELECT * FROM bxj_objects_t3;
ON DEMAND和ON COMMIT物化视图的区别
ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同,
ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;
而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。
物化视图是物理表的么?
1) 物化视图在某种意义上说就是一个物理表(但并不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证;
2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;
3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证。
查询物化视图上次刷新时间
SELECT last_refresh_date
FROM user_mviews
WHERE mview_name = 'MV_TABLENAME
*/