创建更新物化视图的Job:
begin
IF OracleObjectMM.f_CheckVMViewExists('vm_dj_plan') = 1 thenexecute immediate 'drop MATERIALIZED view vm_dj_plan';
end if;
execute immediate ' CREATE MATERIALIZED VIEW VM_DJ_Plan
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
as
select * From v_dj_planresult where Active_YN = ''1''';
--建了索引变慢。
execute immediate 'create Index I_VM_DJPLanID On VM_DJ_PLAN(DJ_plan_ID)';
end;
/
begin
IF OracleObjectMM.f_CheckVMViewExists('vm_dj_plan') = 1 then
execute immediate 'drop MATERIALIZED view vm_dj_plan';
end if;
execute immediate ' CREATE MATERIALIZED VIEW VM_DJ_Plan
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
as
select * From v_dj_planresult where Active_YN = ''1''';
--建了索引变慢。
execute immediate 'create Index I_VM_DJPLanID On VM_DJ_PLAN(DJ_plan_ID)';
end;
/
--漏检的
-- 每10分钟更新一次
--漏检专用点检计划物化视图
begin
IF OracleObjectMM.f_CheckVMViewExists('vm_dj_planUncheck') = 1 then
execute immediate 'drop MATERIALIZED view vm_dj_planUncheck';
end if;
execute immediate ' CREATE MATERIALIZED VIEW vm_dj_planUncheck
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
as
select * From DJ_plan where Active_YN = ''1''
and Absence_Yn = ''1''';
--建了索引变慢。
--execute immediate 'create Index I_vm_dj_planUncheckDJPLanID On vm_dj_planUncheck(DJ_plan_ID)';
end;
/