物化视图操作示例

物化视图操作示例


1.     创建物化视图需要的权限：


[sql] view plaincopyprint?
01.GRANT CREATE MATERIALIZED VIEW TO USER_NAME; 
GRANT CREATE MATERIALIZED VIEW TO USER_NAME;



2.  在源表建立物化视图日志 


[sql] view plaincopyprint?
01.CREATE MATERIALIZED VIEW LOG ON DAVE 
02.TABLESPACE&BISONCU_SPACE           -- 日志空间  
03.WITH PRIMARY KEY;                   -- 指定为主键类型 
CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE           -- 日志空间
WITH PRIMARY KEY;                   -- 指定为主键类型


3.  授权给中间用户 


[sql] view plaincopyprint?
01.GRANT SELECT ON DAVE TO ANQING; 
02.GRANT SELECT ON MLOG$_DAVE TO ANQING; 
GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;



4.  在目标数据库上创建MATERIALIZED VIEW 




[sql] view plaincopyprint?
01.CREATE MATERIALIZED VIEW AICS_DAVE 
02.TABLESPACE&BISONCS_SPACE 
03.REFRESH FAST 
04.   ON DEMAND 
05.   --第一次刷新时间  
06.   --START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')  
07.   START WITH SYSDATE 
08.   --刷新时间间隔。每1天刷新一次，时间为凌晨2点  
09.   --NEXT TRUNC(SYSDATE,'dd')+1+2/24  
10.   NEXT SYSDATE+1/24/20 
11.WITH PRIMARY KEY 
12.--USING DEFAULT LOCAL ROLLBACKSEGMENT  
13.DISABLE QUERY REWRITE AS 
14.SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION 
15.FROM AICS_DAVE@LINK_DAVE; 
CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
   ON DEMAND
   --第一次刷新时间
   --START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
   START WITH SYSDATE
   --刷新时间间隔。每1天刷新一次，时间为凌晨2点
   --NEXT TRUNC(SYSDATE,'dd')+1+2/24
   NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM AICS_DAVE@LINK_DAVE;


5.  在目标物化视图上创建索引




[sql] view plaincopyprint?
01.CREATE INDEX IDX_T_DV_CT 
02.   ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME) 
03.   TABLESPACE &BISON_IDX; 
04.  
05.CREATE INDEX IDX_T_DV_UT 
06.   ON AICS_DEV_INFO (UPDATE_TIME) 
07.   TABLESPACE &BISON_IDX; 
08.  
09.CREATE INDEX I_T_DV_MSISDN 
10.   ON AICS_DEV_INFO (MSISDN) 
11.   TABLESPACE &BISON_IDX; 
CREATE INDEX IDX_T_DV_CT
   ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
   TABLESPACE &BISON_IDX;

CREATE INDEX IDX_T_DV_UT
   ON AICS_DEV_INFO (UPDATE_TIME)
   TABLESPACE &BISON_IDX;

CREATE INDEX I_T_DV_MSISDN
   ON AICS_DEV_INFO (MSISDN)
   TABLESPACE &BISON_IDX;





6. 物化视图刷新说明

（1）使用dbms_mview.refresh 手工刷新

如：


[sql] view plaincopyprint?
01.EXEC DBMS_MVIEW.REFRESH('MV_DAVE'); 
02.  
03.--完全刷新  
04.EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c'); 
05.EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C'); 
06.  
07.--快速刷新  
08.EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f'); 
09.EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F'); 
EXEC DBMS_MVIEW.REFRESH('MV_DAVE');

--完全刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');

--快速刷新
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');





（2）使用dbms_refresh.refresh 过程来批量刷新MV

          如果我们在创建物化视图的过程指定start 和next time的刷新时间，那么Oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。

            使用这种方式刷新之前需要先make refresh group，然后才可以刷新。



            Refreshmake 的语法可以参考：

http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057



示例：

            假设存在物化视图MV_T1, MV_T2, MV_T3. 创建refresh group的语法如下：


[sql] view plaincopyprint?
01.SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1') 
02.  
03.--刷新整个refresh group 组：  
04.SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST') 
SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')

--刷新整个refresh group 组：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')


7. 删除物化视图及日志


[sql] view plaincopyprint?
01.--删除物化视图日志：  
02.DROP MATERIALIZED VIEW LOG ON DAVE; 
03.--删除物化视图  
04.DROP MATERIALIZED VIEW MV_DAVE; 
--删除物化视图日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--删除物化视图
DROP MATERIALIZED VIEW MV_DAVE;


8. 查看物化视图刷新状态信息




[sql] view plaincopyprint?
01.SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS; 
02.SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES; 
SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;


9. 查询物化视图日志：




[sql] view plaincopyprint?
01.SELECT * FROM MLOG$_DAVE;