oracle物化视图

参考资料：oracle物化视图总结

创建物化视图日志：

CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS;
SELECT * FROM ALL_MVIEW_LOGS;   --MLOG$_DEPARTMENTS

创建物化视图日志的标准语句为：

create_materialized_vw_log::=
CREATE MATERIALIZED VIEW LOG
  ON [ schema. ] table
  [ physical_attributes_clause
  | TABLESPACE tablespace
  | logging_clause
  | { CACHE | NOCACHE }
    [ physical_attributes_clause
    | TABLESPACE tablespace
    | logging_clause
    | { CACHE | NOCACHE }
    ]...
  ]
  [ parallel_clause ]
  [ table_partitioning_clauses ]
  [ WITH { OBJECT ID
         | PRIMARY KEY
         | ROWID
         | SEQUENCE
         | (column [, column ]...)
         }
           [, { OBJECT ID
              | PRIMARY KEY
              | ROWID
              | SEQUENCE
              | (column [, column ]...)
              }
           ]...
    [ new_values_clause ]
  ] ;
  
  physical_attributes_clause::=
  [ { PCTFREE integer
  | PCTUSED integer
  | INITRANS integer
  | storage_clause
  }
    [ PCTFREE integer
    | PCTUSED integer
    | INITRANS integer
    | storage_clause
    ]...
]

logging_clause::=
{ LOGGING | NOLOGGING }

parallel_clause::=
{ NOPARALLEL | PARALLEL [ integer ] }

new_values_clause::=
{ INCLUDING | EXCLUDING } NEW VALUES

常用创建物化视图语法（标准语句详细见oracle 文档）：

CREATE MATERIALIZED VIEW [view_name] 
[ ON PREBUILT TABLE [ { WITH | WITHOUT } REDUCED PRECISION ] 
       | [BUILD { IMMEDIATE | DEFERRED }]    ]
[REFRESH [FAST|COMPLETE|FORCE] ]
[ ON COMMIT|DEMAND] 
[START WITH (start_time) NEXT (next_time) ]
[WITH {PRIMARY KEY | ROWID}]
[ { DISABLE | ENABLE } QUERY REWRITE]
AS subquery

　　

实例：

create materialized view TEMP_MV
refresh complete on demand
start with to_date('25-10-2020 23:50:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate+1,'dd')+interval '23:50:00' hour to second 
as
select * from temp

　　刷新物化视图

--快速增量刷新物化视图
BEGIN
  DBMS_MVIEW.refresh('TEMP_MV','F');
END;

--查询物化视图
SELECT * FROM temp_mv;

　　删除物化视图

--ORCL HR
DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;
--ORCL1 SCOTT
DROP MATERIALIZED VIEW temp_mv;