Oracle物化视图

创建物化视图举例：

create materialized view xxx_tan

refresh force on demand
as
select district_id,agcycompany_id,hospital_id,doctor_id,salesman_id,sample_time,
(case  when
    instr( tang_result,'1st')>0 and instr( tang_result,'1st',1,2)=0 and instr(tang_result,'2nd')=0
  then 1
  when
   instr( tang_result,'2nd')>0 and instr( tang_result,'2nd',1,2)=0 and instr(tang_result,'1st')=0
  then 2
  when
   (tang_result is null or  tang_result=';' or tang_result='；')
  then 3
  when
    ( TANG_RESULT like '%未做%' or TANG_RESULT like  '%未作%'
                   or  upper(replace(TANG_RESULT,' ','')) like '%NOTTODO%' or ( upper(TANG_RESULT) like 'NO' and  (LENGTH(TANG_RESULT)<4)))
                   and  P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT)!=1 and P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT)!=2
  then 4
end

) tang_flag,P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT) tang_result_flag
,count(*) nums
from HIMS_NIFTY_SAMPLE_ALL group by district_id,agcycompany_id,hospital_id,doctor_id,salesman_id,sample_time,
(case  when
    instr( tang_result,'1st')>0 and instr( tang_result,'1st',1,2)=0 and instr(tang_result,'2nd')=0
  then 1
  when
   instr( tang_result,'2nd')>0 and instr( tang_result,'2nd',1,2)=0 and instr(tang_result,'1st')=0
  then 2
  when
   (tang_result is null or  tang_result=';' or tang_result='；')
  then 3
  when
   ( TANG_RESULT like '%未做%' or TANG_RESULT like  '%未作%'
                   or  upper(replace(TANG_RESULT,' ','')) like '%NOTTODO%' or ( upper(TANG_RESULT) like 'NO' and  (LENGTH(TANG_RESULT)<4)))
                   and  P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT)!=1 and P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT)!=2
  then 4
end

),P_NIFTY_COMMON.F_GET_TANG_RESULT_FLAG(TANG_RESULT);

 

更新物化视图举例：

call dbms_mview.refresh('xxx_tan');

 

删除物化视图举例：

drop materialized view xxx_tan;

当时由于物化视图是建立在另外一张视图上的，结果速度很慢。后来先尝试把另外的那张视图换成物化视图，还是很慢，最后干脆把另外那张基础的用实体表来做，最后速度才快了。所以要综合看物化视图和实体表，有时候不一定物化视图的效率比表高