Oracle示例物化视图

-- 数据测试 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

 

 

*/