Advertisement
chameleonrising

Oracle-Materialized View

Sep 4th, 2019
1,154
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE MATERIALIZED VIEW TMI_ISD_MV_FLOW_STATUS_TEMP
  2. AS
  3.   SELECT wvl.system_matter_id ,
  4.     xLast.CREATE_DATE ,
  5.     xLast.NODE_NAME ,
  6.     xLast.NODE_TYPE
  7.   FROM TMI_ISD_WV_LINK_REV4 wvl
  8.   LEFT JOIN
  9.     (SELECT xRN.CREATE_DATE,
  10.       xRN.NODE_NAME,
  11.       xRN.NODE_TYPE,
  12.       xRN.system_matter_id
  13.     FROM
  14.       (SELECT t.CREATE_DATE,
  15.         t.NODE_NAME,
  16.         t.NODE_TYPE,
  17.         t.system_matter_id ,
  18.         ROW_NUMBER() OVER ( PARTITION BY t.system_matter_id ORDER BY t.CREATE_DATE DESC ) num
  19.       FROM TMI_ISD_FLOW_STATUS t
  20.       ) xRN
  21.     WHERE num = 1
  22.     ) xLast
  23.   ON xLast.SYSTEM_MATTER_ID = wvl.SYSTEM_MATTER_ID;
  24. CREATE INDEX ixTMI_ISD_MV_FLOW_STATUS_TEMP ON TMI_ISD_MV_FLOW_STATUS_TEMP ( SYSTEM_MATTER_ID );
  25. ALTER INDEX IMART_SYSTEM.ixTMI_ISD_MV_FLOW_STATUS_TEMP rebuild ONLINE;
  26. CREATE OR REPLACE PROCEDURE "SP_TMI_ISD_MV_FLOW_STATUS_TEMP" AS
  27. BEGIN
  28.     DBMS_MVIEW.REFRESH(LIST=>'TMI_ISD_MV_FLOW_STATUS_TEMP',PARALLELISM=>4);
  29. END;
  30. BEGIN
  31.     DBMS_SCHEDULER.CREATE_JOB (
  32.          job_name             => 'JOB_TMI_ISD_MV_FLOW_STATUS',
  33.          job_type             => 'PLSQL_BLOCK',
  34.          job_action           => 'SP_TMI_ISD_MV_FLOW_STATUS_TEMP;',
  35.          start_date           => TIMESTAMP '2019-09-02 17:00:00',
  36.          repeat_interval      => 'FREQ=MINUTELY;INTERVAL=5;',
  37.          enabled              => TRUE);
  38. END;
  39. EXECUTE SP_TMI_ISD_MV_FLOW_STATUS_TEMP;COMMIT;
Advertisement
RAW Paste Data Copied
Advertisement