Oracle物化视图

CREATE   MATERIALIZED VIEW AHSZY.WV_RLDW_C
(
  "YEAR",
  "MONTH",
  "DAY",
  DAYS,
  DFAGNM,
  FWQT,
  QSNAME,
  QSID
)
BUILD DEFERRED --延迟刷新不立即刷新
refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新
on demand --按照指定方式刷新
start with to_date('27-4-2010 22:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间
next  1+SYSDATE
AS
  SELECT  WR_RLTW_C2.YEAR AS YEAR,WR_RLTW_C2.MONTH AS MONTH,WR_RLTW_C2.DAY AS DAY,
  TO_CHAR(WR_RLTW_C2.YEAR)||'年'||TO_CHAR( WR_RLTW_C2.MONTH)||'月'||TO_CHAR( WR_RLTW_C2.DAY)||'日'  AS DAYS,WR_DFAG_B.DFAGNM,
  sum(WR_RLTW_C2.FWQT) as FWQT, WS_QSOR.QSNAME,WS_QSOR.QSID
   FROM  WR_RLTW_C2, WS_QSOR, WR_DFAG_B
   WHERE WS_QSOR.QUSHUIRENID = WR_DFAG_B.DFAGCD AND WS_QSOR.QSID = WR_RLTW_C2.DFAGCD
    GROUP BY WR_RLTW_C2.YEAR,WR_RLTW_C2.MONTH,WR_RLTW_C2.DAY,WS_QSOR.QSNAME,WR_DFAG_B.DFAGNM,WS_QSOR.QSID order by WS_QSOR.QSNAME;

 

--删除物化视图：
--删除日志： DROP materialized view log on mv_lvy_levytaxbgtdiv;
DROP materialized view log on tb_lvy_levydetaildata;
DROP materialized view log on tb_lvy_levydata;
--删除物化视图 drop materialized view MV_LVY_LEVYDETAILDATA;
--基本和对表的操作一致 --物化视图由于是物理真实存在的，故可以创建索引。
创建方式和对普通表创建方式相同，就不在重复写了。