物化视图管理

1 包含子查询的物化的物化视图创建

create materialized view MV_OLT

refresh force on demand

start with to_date('15-11-2018 17:00:12', 'dd-mm-yyyy hh24:mi:ss') next sysdate+1/24

as

select * from v11;

CREATE OR REPLACE VIEW V11 AS

select

vpr.oltname oltname,

       vpr.portname oltportname,

       vpr.portrmuid transoltport,

       (SELECT vifps.fldname

          FROM NRMS_TJ.VIEW@tj vifps

         WHERE vpr.SCHEME_NAME = vifps.fldid) as ponname,

  FROM adn.V_RES@tj vpr

---

 

分区形式物化视图 

CREATE Materialized VIEW xxx(event_time) 
partition by range(event_time)
(
partition p1   values less than (to_date('2015-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p2   values less than  (to_date('2015-02-02 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p3   values less than (to_date('2015-02-03 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p4   values less than (to_date('2015-02-04 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p5   values less than (to_date('2015-02-05 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p6   values less than (to_date('2015-02-06 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p7   values less than (to_date('2015-02-07 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p8   values less than (to_date('2015-02-08 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p9   values less than (to_date('2015-02-09 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p10 values less than (to_date('2015-02-10 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p11 values less than (to_date('2015-02-11 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p12 values less than (to_date('2015-02-12 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p13 values less than (to_date('2015-02-13 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p14 values less than (to_date('2015-02-14 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p15 values less than (to_date('2015-02-15 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p16 values less than (to_date('2015-02-16 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p17 values less than (to_date('2015-02-17 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p18 values less than (to_date('2015-02-18 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p19 values less than (to_date('2015-02-19 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p20 values less than (to_date('2015-02-20 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p21 values less than(to_date('2015-02-21 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p21 values less than (to_date('2015-02-22 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p23 values less than (to_date('2015-02-23 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p24 values less than (to_date('2015-02-24 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p25 values less than (to_date('2015-02-25 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p26 values less than (to_date('2015-02-26 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p27 values less than (to_date('2015-02-27 00:00:00','yyyy-mm-dd hh24:mi:ss')),
partition p28 values less than(to_date('2015-02-28 00:00:00','yyyy-mm-dd hh24:mi:ss'))
)
refresh fast
on demand
start with sysdate next sysdate+1/1440
with rowid
as
SELECT
date from 
FROM  TFA t1 where event_time>=trunc(sysdate,'MONTH') and event_time<=last_day(trunc(sysdate,'MONTH'))