年报物化视图建图语句

Create materialized view log on F_RPT_TASK
TABLESPACE CISAISY 
WITH PRIMARY KEY 
 ----tecam01为表名 ----注：(tecam01为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图 ----创建物化视图语句：
 /*删除日志和视图*/
  DROP materialized view log on  F_RPT_TASK
  drop materialized view MV_F_RPT_TASK

 
  Create materialized view MV_F_RPT_TASK
  TABLESPACE CISAISY
  BUILD DEFERRED
  refresh force
 on demand
 start with  to_date('02-01-2014 12:00:10', 'dd-mm-yyyy hh24:mi:ss') 
 next  sysdate+5/(24*60)
   As
  SELECT * FROM F_RPT_TASK
 WHERE  to_number(RPT_PERIOD)  >= to_number(to_char((sysdate),'yyyy' ) -2);
 
 

===================自动增量刷新========== /*删除日志和视图*/ DROP materialized view log on F_RPT_TASK drop materialized view MV_F_RPT_TASKCreate materialized view log on F_RPT_TASK TABLESPACE CISAISY WITH PRIMARY KEY Create materialized view MV_F_RPT_TASK ----MVTEST为物化视图名 Build immediate ----创建时生成数据对应的是build deferred Refresh fast ----增量刷新 On commit ----在基表有更新时提交，这里该句对视图无效 With PRIMARY KEY ----这里创建基于primary key 的物化视图，要与日志中对应的是 primary key ；若日志为 rowid 则此处必须为rowid As Select * from F_RPT_TASK where RPT_PERIOD='2013' or RPT_PERIOD ='2012';