Oracle：为什么在基础视图中的数据更改后，我的MW无效到NEEDS_COMPILE状态

我们使用以下物化视图和基础视图来实现一些报告 . 物化视图完全从我们的应用程序手动刷新（通过DBMS_SNAPSHOT.REFRESH（'“OVERALL_WEEKLY”'，'C'）;） . 在此调用之后，视图处于FRESH状态，但在对基础表执行任何DML操作之后，实例化视图将获得NEEDS_COMPILE状态 . 对视图的查询如下 . 不确定的表EBR_CYCLE_TIME和EBR_AREA正在经常变化 . 因此，提交时刷新不是我们的目的 .

有没有办法避免NEEDS_COMPILE状态？或者更好的是导致NEED_COMPILE状态的原因？

CREATE OR REPLACE FORCE VIEW "OTD_WEEKLY" AS
  SELECT
    otd.WEEK,
    otd.SITE_ID,
    otd.AREA_ID,
    otd.OTD_METRIC                                               AS CT_METRIC,
    ROUND(100 * (SUM(otd.SUCCESS) / SUM(otd.CT_TOTAL_COUNT)), 2) AS OTD_VALUE
  FROM
    (SELECT
       FC.MFL_FISCAL_YR_NUM * 100 + FC.MFL_FISCAL_WK_NUM AS WEEK,
       r.BUSINESS_UNIT_ID                                AS PAL2_ID,
       a.site_id                                         AS SITE_ID,
       a.area_def_id                                     AS AREA_ID,
       a.PRIORITY                                        AS PRIORITY,
       r.EBR_BUILD_SUBTYPE                               AS NPI,
       r.CORPORATE_TD                                    AS CTD,
       ctd.NAME                                          AS OTD_METRIC,
       COUNT(r.ebr_number)                               AS CT_TOTAL_COUNT,
       COUNT(
           CASE
           WHEN (ct.TIME_ELAPSED) > (ct.TARGET * 86400)
             THEN NULL
           ELSE r.ebr_number
           END)                                          AS SUCCESS,
       COUNT(
           CASE
           WHEN (ct.TIME_ELAPSED) > (ct.TARGET * 86400)
             THEN r.ebr_number
           ELSE NULL
           END)                                          AS MISSED,
       COUNT(DISTINCT r.ebr_number)                      AS TOTAL_NUMBER_OF_EBR
     FROM ebr_cycle_time ct
       JOIN ebr_area a
         ON (a.id = ct.ebr_area_id
             AND ct.status = 'FINISHED'
             AND a.ship_date IS NOT NULL)
       JOIN ebr_request r
         ON (a.ebr_id = r.id AND r.submit_date >= to_date((select STRING_VALUE from EBR_STATUS_TABLE where key = 'REPORT_DATE_FROM'),'DD.MM.YY'))
       JOIN EBR_GROUP_CYCLE_TIME_DEF gctd
         ON (ct.CYCLE_TIME_GROUP_DEF = gctd.ID)
       JOIN EBR_CYCLE_TIME_DEF ctd
         ON (ctd.ID = gctd.CYCLE_TIME_DEF_ID
             AND ctd.OTD_METRIC = 'Y')
       JOIN EBR_CYCLE_TIME_GROUP ctg
         ON (ctg.id = gctd.CYCLE_TIME_GROUP_ID)
       JOIN EEBR_MC_LCL_FISCAL FC
         ON (FC.MFL_QUERY_DT = TRUNC(a.ship_date) AND fc.MFL_QUERY_DT > add_months(trunc(sysdate, 'MM'), -8) AND fc.MFL_QUERY_DT <= sysdate)
     GROUP BY
       r.BUSINESS_UNIT_ID,
       a.site_id,
       a.area_def_id,
       ctd.name,
       fc.mfl_fiscal_yr_num,
       fc.mfl_fiscal_wk_num,
       ct.target,
       a.PRIORITY,
       r.CORPORATE_TD,
       r.EBR_BUILD_SUBTYPE
    ) otd
  GROUP BY
    otd.WEEK,
    otd.PAL2_ID,
    otd.SITE_ID,
    otd.AREA_ID,
    otd.OTD_METRIC;


CREATE OR REPLACE FORCE VIEW "LAGGING_SCORE_WEEKLY" AS
  SELECT
    dsc.SITE_ID,
    dsc.AREA_ID,
    dsc.CT_METRIC,
    (dsc.ARCHIVE_YEAR * 100 + dsc.ARCHIVE_WEEK) AS WEEK,
    ROUND(AVG(SCORE), 4)                        AS SCORE
  FROM
    (SELECT
       cts.site_id                                         AS SITE_ID,
       ls.AREA_DEF_ID                                      AS AREA_ID,
       ctd.name                                            AS CT_METRIC,
       ctd.id                                              AS CT_ID,
       fc.MFL_QUERY_DT                                     AS ARCHIVE_DAY,
       fc.MFL_FISCAL_WK_NUM                                AS ARCHIVE_WEEK,
       fc.MFL_FISCAL_MTH_NUM                               AS ARCHIVE_MONTH,
       fc.MFL_FISCAL_YR_NUM                                AS ARCHIVE_YEAR,
       (fc.MFL_FISCAL_YR_NUM * 100 + fc.MFL_FISCAL_WK_NUM) AS WEEK,
       CASE
       WHEN SUM(cts.PENALTY) > 0
         THEN SUM(cts.PENALTY)
       ELSE 0
       END                                                 AS EBR_PENALTY,
       COUNT(DISTINCT cts.ebr_number)                      AS NUMBER_OF_EBR,
       COUNT(DISTINCT (
         CASE
         WHEN cts.LAGGING_TIME > 0
           THEN cts.ebr_number
         ELSE NULL
         END))                                             AS NUMBER_OF_LAGGING_EBR,
       CASE
       WHEN SUM(cts.PENALTY) > 0
         THEN greatest(100 - 100 * (SUM(cts.PENALTY) / COUNT(DISTINCT cts.ebr_number)), 0)
       ELSE 100
       END                                                 AS SCORE
     FROM EBR_CYCLE_TIME_SNAPSHOT cts
       JOIN EBR_REQUEST r
         ON (r.ebr_number = cts.ebr_number AND r.submit_date >= to_date((select STRING_VALUE from EBR_STATUS_TABLE where key = 'REPORT_DATE_FROM'),'DD.MM.YY'))
       RIGHT JOIN EBR_LAGGING_STATISTIC ls
         ON ((TRUNC(ls.stat_date) = TRUNC(cts.SNAPSHOT_TIME))
             AND ls.site_id = cts.site_id
             AND cts.AREA_DEF_ID = ls.AREA_DEF_ID
             AND ls.CT_DEF_ID = cts.CYCLE_TIME_DEF_ID)
       JOIN EBR_CYCLE_TIME_DEF ctd
         ON (ls.CT_DEF_ID = ctd.id
             AND ctd.OTD_METRIC = 'Y')
       JOIN EEBR_MC_LCL_FISCAL fc
         ON (TRUNC(ls.STAT_DATE) = TRUNC(fc.MFL_QUERY_DT) AND fc.MFL_QUERY_DT > add_months(trunc(sysdate, 'MM'), -8))
     GROUP BY
       cts.site_id,
       ls.AREA_DEF_ID,
       ctd.name,
       ctd.id,
       fc.MFL_QUERY_DT,
       fc.MFL_FISCAL_WK_NUM,
       fc.MFL_FISCAL_MTH_NUM,
       fc.MFL_FISCAL_YR_NUM,
       ls.NUMBER_OF_EBR,
       ls.NUMBER_OF_LAGGING_EBR,
       TRUNC(ls.STAT_DATE)
    ) dsc
  GROUP BY dsc.SITE_ID,
    dsc.AREA_ID,
    dsc.CT_METRIC,
    dsc.ARCHIVE_WEEK,
    dsc.ARCHIVE_MONTH,
    dsc.ARCHIVE_YEAR;


CREATE OR REPLACE FORCE VIEW "START_COMPLIANCE_WEEKLY" AS
  SELECT
    'Starts Compliance'                                        AS CT_METRIC,
    a.site_id                                                 AS SITE_ID,
    a.area_def_id                                             AS AREA_ID,
    ((lstw.MFL_FISCAL_YR_NUM * 100) + lstw.MFL_FISCAL_WK_NUM) AS WEEK,
    ROUND(AVG(
              CASE
              WHEN ((ct.START_DATE IS NOT NULL
                     AND TRUNC((ct.START_DATE AT TIME ZONE 'MST') AT TIME ZONE s.time_zone) >
                         TRUNC((a.FIRST_FIRM_START_DATE AT TIME ZONE 'MST') AT TIME ZONE s.time_zone))
                    OR (ct.START_DATE IS NULL
                        AND next_day(TRUNC(sysdate) - 7, 'Sun') >
                            TRUNC((a.FIRST_FIRM_START_DATE AT TIME ZONE 'MST') AT TIME ZONE s.time_zone)))
                THEN 0
              ELSE 100
              END), 2)                                        AS SCORE
  FROM ebr_area a
    JOIN ebr_request r
      ON (a.ebr_id = r.id AND a.FIRST_FIRM_START_DATE IS NOT NULL
          AND a.FIRST_FIRM_START_DATE <= next_day(TRUNC(sysdate) - 7, 'Sun') AND AND r.status <> 'CANCELLED'
          AND r.submit_date >= to_date((select STRING_VALUE from EBR_STATUS_TABLE where key = 'REPORT_DATE_FROM'),'DD.MM.YY'))
    JOIN ebr_site s
      ON (s.id = a.site_id)
    LEFT JOIN
    (SELECT
       ct.START_DATE  AS START_DATE,
       ct.ROUND       AS ROUND,
       ct.ebr_area_id AS area_id
     FROM ebr_cycle_time ct
       JOIN EBR_GROUP_CYCLE_TIME_DEF gctd
         ON (ct.CYCLE_TIME_GROUP_DEF = gctd.ID
             AND ct.status <> 'NEW')
       JOIN EBR_CYCLE_TIME_DEF ctd
         ON (ctd.ID = gctd.CYCLE_TIME_DEF_ID
             AND ctd.code = 'SITE_PROCESSING')
    ) ct ON (ct.area_id = a.id)
    JOIN EEBR_MC_LCL_FISCAL lstw
      ON (lstw.MFL_QUERY_DT = TRUNC(FIRST_FIRM_START_DATE) AND lstw.MFL_QUERY_DT > add_months(trunc(sysdate, 'MM'), -8))
  GROUP BY
    a.site_id,
    s.time_zone,
    a.area_def_id,
    lstw.MFL_FISCAL_YR_NUM,
    lstw.MFL_FISCAL_WK_NUM;


CREATE MATERIALIZED VIEW "OVERALL_WEEKLY"
  AS SELECT s.code AS SITE_CODE,
  s.name      AS SITE_NAME,
  reports.SITE_ID,
  ad.NAME AS AREA,
  ad.CODE AS AREA_CODE,
  reports.AREA_ID,
  reports.REPORT_TYPE,
   reports.CT_METRIC,
  reports.WEEK,
  reports.SCORE
FROM (
  (SELECT 'Starts Compliance' AS REPORT_TYPE,
    AREA_ID,
    SITE_ID,
    CT_METRIC,
    WEEK,
    SCORE
  FROM START_COMPLIANCE_WEEKLY
  )
UNION
  (SELECT 'OTD' AS REPORT_TYPE,
    AREA_ID,
    SITE_ID,
    OTD_WEEKLY.CT_METRIC,
    OTD_WEEKLY.WEEK,
    OTD_WEEKLY.OTD_VALUE AS SCORE
  FROM OTD_WEEKLY
  )
UNION
  (SELECT 'Lagging' AS REPORT_TYPE,
    AREA_ID,
    SITE_ID,
    LAGGING_SCORE_WEEKLY.CT_METRIC,
    LAGGING_SCORE_WEEKLY.WEEK,
    LAGGING_SCORE_WEEKLY.SCORE
  FROM LAGGING_SCORE_WEEKLY
  )) reports
JOIN EBR_SITE s
ON (s.id = reports.SITE_ID)
JOIN EBR_AREA_DEF ad
ON (ad.id = reports.area_id);