物化视图的容易失效的一种情况解决

      以下是我做过的一个物化视图的脚本,费了几番周折,问题总算解决了,但我一直无法解释其原因:

      count((combine_time - in_time) * 24) count_lead_time,  本身这一列是没有应用价值的,这一列在没有加上之前,物化视无法自动刷新,会自动失效,加上之后,就没问题了.

 

 

CREATE MATERIALIZED VIEW MV_OCR_COMBINE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "WIPDATA"
  BUILD IMMEDIATE
  REFRESH FAST ON DEMAND START WITH sysdate+0 NEXT trunc(sysdate+1)
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS
 select trunc(combine_time) combine_time,
       proj_code,
       update_user,
       barcode,
       lbar_2nd+mbar_2nd count_2nd,
       count(*) count_rowbar,
       sum((combine_time - in_time) * 24) lead_time,
       count((combine_time - in_time) * 24) count_lead_time,  --去掉这一列,物化视将无法自动刷新
       sum(hold_time) hold_time,
       count(hold_time) count_hold_time
  from ocr_combine
 group by trunc(combine_time), proj_code, update_user,barcode,lbar_2nd+mbar_2nd;