Oracle物化视图 materialized view 相关操作汇总

---

Oracle物化视图（materialized views）简介

1. Oracle物化视图（materialized views）是一种特殊的视图，它的结果集被存储在表中，而不是像普通视图一样只是包含一条查询语句。
2. 物化视图可以提高查询性能，在查询复杂、关联表较多的情况下，通过预计算结果并缓存到物化视图的表中，可以避免每次查询都需要重新计算结果的情况，从而加快查询速度。

Ⅰ、Oracle物化视图可以分为以下几种类型

1、基本物化视图

直接从查询结果创建的普通物化视图。

2、带有聚集函数的物化视图

将结果集按照指定的聚集函数（如SUM、COUNT、AVG等）分组后创建的物化视图。

3、带有连接关系的物化视图

包含多个表之间的连接关系的物化视图。

Ⅱ、在使用物化视图时，注意事项

1、物化视图需要定期刷新，否则数据会过期。可以通过手动或自动刷新来更新数据。

2、物化视图的更新操作会影响到相关基础表的数据。

3、物化视图的存储空间和刷新频率需要根据实际情况进行注意管理，以确保性能和数据一致性的平衡。

4、创建物化视图，会自动创建一个Oracle Job任务来定期执行该物化视图刷新操作

创建物化视图，会自动创建一个Oracle Job任务来定期执行该物化视图刷新操作

---

Oracle物化视图（materialized views）实操

一、创建物化视图

create materialized view [view_name] 
build [immediate|deferred]
refresh [fast|complete|force] 
[ 
on [commit|demand] | 
start with (start_time) next (next_time) 
] 
 [enable | disable] query rewrite
as 
{创建物化视图用的查询语句} 

二、删除物化表的日志表

DROP MATERIALIZED VIEW LOG ON 表名;

三、为将要创建物化视图的表添加带主键的日志表

CREATE MATERIALIZED VIEW LOG ON 表名 WITH PRIMARY KEY;

四、创建物化视图并每分钟刷新一次

执行下列SQL，创建物化视图：

CREATE MATERIALIZED VIEW 表名 
NOLOGGING REFRESH FAST ON DEMAND 
WITH PRIMARY KEY 
START WITH SYSDATE NEXT SYSDATE +1/(20*64) AS 
SELECT * FROM 表名;

CREATE MATERIALIZED VIEW TZQ.MV_TZQ_INFO_TYPE
REFRESH FORCE ON DEMAND
START WITH TO_DATE('2023-09-05 09:53:00', 'YYYY-MM-DD HH24:MI:SS') NEXT SYSDATE+0.5/24 
AS
select * from INFO_TYPE t;

验证是否创建成功

SELECT * FROM 表名；

五、删除物化视图

DROP MATERIALIZED VIEW 表名;

六、刷新物化视图

BEGIN
   DBMS_MVIEW.REFRESH('MV_TZQ_LOG');
END;

七、物化视图对应的系统视图

SELECT * FROM user_mviews WHERE mview_name = 'MV_TZQ_DEAL';
SELECT * FROM dba_mviews WHERE mview_name = 'MV_TZQ_DEAL';

八、查当前运行的 job 信息

SELECT d.schema_user
      ,d.what
      ,d.interval
      ,d.last_date,d.last_sec
      ,d.next_date,d.next_sec
      ,r.* 
  FROM Dba_Jobs_Running r
  LEFT JOIN dba_jobs d
    ON r.job = d.job;

九、查 job 信息

SELECT * from Dba_Jobs t WHERE t.what LIKE '%MV_TZQ_DEAL%';
SELECT * FROM dba_jobs;
SELECT * FROM user_jobs;

十、手动运行 job

BEGIN
  dbms_job.run(403); --10:24
  COMMIT;
END;

十一、停止运行中的job

BEGIN 
  dbms_scheduler.stop_job(job_name => 'job_name_123');
END;