物化视图应用案例实战

因项目过程中 视图查询 导致整个复杂报表响应十分慢。
注：因该视图 里面逻辑较为复杂，且存在视图套视图多层。

为了提升改视图查询效率，考虑汇总后数据量不大，且考虑不影响视图内部逻辑等问题。
处理办法 将改部分慢的sql 形成 物化视图+定时刷新 以达到解决慢的问题(用表代替视图测试，亲测 能做到正常刷新)

1、建表测试

create table WBHZ_NUM_JIAC
(
  IS_DATE_A          VARCHAR2(100),
  DEPT_CODE_A        VARCHAR2(100),
  DEPT_NAME          VARCHAR2(100),
  DEPTOWN            VARCHAR2(10),
  PARENT_DEPT_NAME_A VARCHAR2(100),
  IS_NUM_A           NUMBER(6),
  NO_WB_A            NUMBER(6),
  YES_WB_A           NUMBER(6),
  IS_NUM_B           NUMBER(6)
);
insert into wbhz_num_jiac (IS_DATE_A, DEPT_CODE_A, DEPT_NAME, DEPTOWN, PARENT_DEPT_NAME_A, IS_NUM_A, NO_WB_A, YES_WB_A, IS_NUM_B)
values ('2023-01', '1001', '测试科室', '1', '测试大科', 100, 50, 40, 30);

2、创建物化视图

CREATE MATERIALIZED VIEW test_wbhz_num_jiac  --test_wbhz_num_jiac为定义名称
REFRESH FORCE ON DEMAND      --默认增量刷新
START WITH sysdate next  sysdate + 1 / (24 * 60)   --从当前时间开始，每分钟运行一次
AS
SELECT * FROM v_wbhz_num_jiac     --对于的sql预计

3、验证物化视图是否自动更新数据

1）修改测试表数据

SELECT * FROM wbhz_num_jiac for  update  ---修改测试表  IS_NUM_A 字段值为 9999，并提交数据。如下图

2） 查询 物化视图数据是否更新

SELECT * FROM test_wbhz_num_jiac    --查询物化视图数据  已同步更新

看到此处，创建物化视图及更新案例已经结束。

注意：一般创建完物化视图后，可查看系统中定义的作业**‘dba_jobs’** 视图，是否出现定时任务计划，如果误删除定时任务，则删除物化视图可重新创建及自动生成。

4、物化视图不能更新的常见情况处理

创建物化视图后，可以手动刷新，却不能自动定时刷新。
BEGIN DBMS_MVIEW.REFRESH('test_wbhz_num_jiac', 'C'); COMMIT; END ;
解决办法 仅供参考

SELECT * FROM  V$PARAMETER WHERE  NAME='job_queue_processes';  -- VALUE 是否为 0 。如果为0 则修改值
alter system set job_queue_processes = 1000;

5、创建存储过程，通过Job任务定时调用存储过程

--创建存储过程 名为pro_wbhz_num_jiac 
CREATE OR REPLACE PROCEDURE pro_wbhz_num_jiac
as 
BEGIN 
  DBMS_MVIEW.REFRESH('test_wbhz_num_jiac', 'C');  --C表示全量刷新物化视图，F表示快速刷新
COMMIT;
END;

--创建定时任务脚本 每分钟执行一次
declare 
job number; 
begin 
dbms_job.submit(job, 'pro_wbhz_num_jiac;', sysdate, 'TRUNC(sysdate,''mi'') + 1/(24*60)'); 
end
commit; 

6、关于dba_jobs视图字段说明

字段（列）	数据类型	描述
JOB	NUMBER	任务的唯一标示号
LOG_USER	VARCHAR2(30)	提交任务的用户
PRIV_USER	VARCHAR2(30)	赋予任务权限的用户
SCHEMA_USER	VARCHAR2(30)	对任务作语法分析的用户模式
LAST_DATE	DATE	最后一次成功运行任务的时间
LAST_SEC	VARCHAR2(8)	如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE	DATE	正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC	VARCHAR2(8)	如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE	DATE	下一次定时运行任务的时间
NEXT_SEC	VARCHAR2(8)	如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME	NUMBER	该任务运行所需要的总时间，单位为秒
BROKEN	VARCHAR2(1)	标志参数，Y标示任务中断，以后不会运行
INTERVAL	VARCHAR2(200)	用于计算下一运行时间的表达式
FAILURES	NUMBER	任务运行连续没有成功的次数
WHAT	VARCHAR2(2000)	执行任务的PL/SQL块
CURRENT_SESSION_LABEL	RAW MLSLABEL	该任务的信任Oracle会话符
CLEARANCE_HI	RAW MLSLABEL	该任务可信任的Oracle最大间隙
CLEARANCE_LO	RAW MLSLABEL	该任务可信任的Oracle最小间隙
NLS_ENV	VARCHAR2(2000)	任务运行的NLS会话设置
MISC_ENV	RAW(32)	任务运行的其他一些会话参数

总结：物化视图这里我也只使用到冰山一脚，也是在记录临时学习到的一些技巧，技术嘛，总是在不断的学习，不断的使用，不断的记录，才会体现他的价值。希望能给大家一些帮助，也希望大家一起交流！！！！