我们使用以下物化视图和基础视图来实现一些报告 . 物化视图完全从我们的应用程序手动刷新(通过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);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
