物化视图,自己研究下吧

DROP MATERIALIZED VIEW EDB.PRODUCTION_MATERIALIZED_VIEW;
CREATE MATERIALIZED VIEW EDB.PRODUCTION_MATERIALIZED_VIEW
(PANELID,TIMEKEY,EVENTTIME,FGCODE,OLDLINE,
OLDPROCESSOPERATIONNAME,PROCESSOPERATIONNAME,REWORKSTARTSTEP,REASONCODE,GRADE,
REVISIONCODE)
TABLESPACE MES_BASIC_DATA
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('13-01-2021 14:41:20','dd-mm-yyyy hh24:mi:ss')
NEXT SYSDATE+15/24/60
WITH PRIMARY KEY
AS
/* 格式化对象 2021/1/13 14:36:56 (QP5 v5.287) */
SELECT /*+INDEX(P0 UN_PH_TIMEKEY_0)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY0@MESDB_LINK P0
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P1 UN_PH_TIMEKEY_1)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY1@MESDB_LINK P1
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P2 UN_PH_TIMEKEY_2)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY2@MESDB_LINK P2
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P3 UN_PH_TIMEKEY_3)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY3@MESDB_LINK P3
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P4 UN_PH_TIMEKEY_4)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY4@MESDB_LINK P4
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P5 UN_PH_TIMEKEY_5)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY5@MESDB_LINK P5
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P6 UN_PH_TIMEKEY_6)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY6@MESDB_LINK P6
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P7 UN_PH_TIMEKEY_7)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY7@MESDB_LINK P7
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P8 UN_PH_TIMEKEY_8)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY8@MESDB_LINK P8
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END
UNION ALL
SELECT /*+INDEX(P9 UN_PH_TIMEKEY_9)*/
PANELID,
TIMEKEY,
EVENTTIME,
FGCODE,
OLDLINE,
OLDPROCESSOPERATIONNAME,
PROCESSOPERATIONNAME,
REWORKSTARTSTEP,
REASONCODE,
GRADE,
REVISIONCODE
FROM PANELHISTORY9@MESDB_LINK P9
WHERE TIMEKEY BETWEEN CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE - 1, 'YYYYMMDD') || '073000000'
END
AND CASE
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) > 730
THEN
TO_CHAR (SYSDATE + 1, 'YYYYMMDD') || '073000000'
WHEN TO_NUMBER (TO_CHAR (SYSDATE, 'HH24MI')) < 730
THEN
TO_CHAR (SYSDATE, 'YYYY/MM/DD') || '073000000'
END;

COMMENT ON MATERIALIZED VIEW EDB.PRODUCTION_MATERIALIZED_VIEW IS 'snapshot table for snapshot EDB.PRODUCTION_MATERIALIZED_VIEW';