oracle优化-使用物化视图

一,创建物化视图

create materialized view MV_CUST_TACCOINFO
refresh complete on demand
start with to_date('08-05-2020 16:01:00', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1/(24*60) 
as
SELECT C_CUSTNO, CUST_ID_UN, C_CUSTNAME, C_CUSTTYPE, C_IDENTITYTYPE,
       C_IDENTITYNO, C_ACCOSTATUS, D_LASTMODIFY, C_FUNDACCO, A.C_AGENCYNO
FROM   CUST_TACCOINFO A;

上面的物化视图是每小时刷新一次.

 

二.假如上面的物化视图无法自动刷新,则可以使用Job刷新

begin
  sys.dbms_scheduler.create_job(job_name            => 'AML.REFRESHMV_CUST_TACCOINFO',
                                job_type            => 'PLSQL_BLOCK',
                                job_action          => 'begin
   dbms_mview.refresh(''MV_CUST_TACCOINFO'');
  end;',
                                start_date          => to_date('06-03-2020 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),