物化视图的使用

手工刷新物化视图

exec dbms_mview.refresh('T_DAR_ARSTATE');

DROP MATERIALIZED VIEW t_dept_view;

DROP MATERIALIZED VIEW t_dept_city_view;

/*城市客户*/

CREATE MATERIALIZED VIEW t_dept_city_view --创建物化视图

 

BUILD IMMEDIATE --在视图编写好后创建

 

AS

 

select *

  from (select TO_dATE(to_char(A.GENERATE_TIME, 'yyyy-MM'), 'YYYY-MM') as v_DATE,

               count(1) v_count,

               B.City_Code

          FROM T_BSE_CUSTOMER A

         RIGHT JOIN T_MGR_DEPT B ON B.UNIFIED_CODE = A.ORG_CODE

         WHERE A.ACTIVE = 'Y'

         group by TO_dATE(to_char(A.GENERATE_TIME, 'yyyy-MM'), 'YYYY-MM'),

                  B.City_Code) t

 ORDER BY v_DATE;

 

 

 

 /*区县客户*/

 CREATE MATERIALIZED VIEW t_dept_view --创建物化视图

BUILD IMMEDIATE --在视图编写好后创建

 

AS

 

select *

  from (select TO_dATE(to_char(A.GENERATE_TIME, 'yyyy-MM'), 'YYYY-MM') as v_DATE,

               count(1) v_count,

               B.County_Code

          FROM T_BSE_CUSTOMER A

         RIGHT JOIN T_MGR_DEPT B ON B.UNIFIED_CODE = A.ORG_CODE

         WHERE A.ACTIVE = 'Y'

         group by TO_dATE(to_char(A.GENERATE_TIME, 'yyyy-MM'), 'YYYY-MM'),

                  B.County_Code) t

 ORDER BY v_DATE;

/*定时器*/

begin

  dbms_scheduler.create_job(job_name => 'refresh_dept_view',

                            job_type => 'plsql_block',

                            job_action => q'`

begin

  dbms_mview.refresh(list => 'T_DEPT_VIEW');

  dbms_mview.refresh(list => 't_dept_city_view');

end;

`',

                            start_date => localtimestamp,

                            repeat_interval => 'freq=daily;bymonthday=1;byhour=0;byminute=05;bysecond=0',

                            enabled => true);

end;

/**查询、删除/

 select owner, job_name, state from dba_scheduler_jobs;

 

  begin

 dbms_scheduler.drop_job(job_name => 'refresh_dept_view',force => TRUE);

 end;