DROP MATERIALIZED VIEW ADR.HL7_STATS_MV;
CREATE MATERIALIZED VIEW ADR.HL7_STATS_MV (MESSAGE_TRANSMISSION_TYPE_ID,STD_INSTITUTION_ID,RECORD_CREATED_DATE,RECORD_COUNT)
TABLESPACE M_DATA_02_P01
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
PARTITION BY RANGE (RECORD_CREATED_DATE)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(  
  PARTITION HL7STATSMV_P01 VALUES LESS THAN (TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE M_DATA_02_P01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                MAXSIZE          UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION VALUES LESS THAN (TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    LOGGING
    NOCOMPRESS 
    TABLESPACE M_DATA_02_P01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128M
                NEXT             128M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
NOCACHE
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH FAST
START WITH TO_DATE('09-08-2017 17:22:00','dd-mm-yyyy hh24:mi:ss')
NEXT TO_DATE('08/09/2017 17:17:00',      'MM-DD-SYYYY HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')+5/1440  
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS 
/* Formatted on 9/29/2017 11:08:01 AM (QP5 v5.269.14213.34769) */
  SELECT MESSAGE_TRANSMISSION_TYPE_ID,
         STD_INSTITUTION_ID,
           (TO_DATE (TO_CHAR (record_created_date, 'MM/DD/YYYY HH24'),
                     'MM/DD/YYYY HH24'))
         + 1 / 24
            record_created_date,
         COUNT (*) RECORD_COUNT
    FROM ADR.hl7_transaction_log
   WHERE hl7_transaction_log_id >= 3699630000
GROUP BY MESSAGE_TRANSMISSION_TYPE_ID,
         STD_INSTITUTION_ID,
           (TO_DATE (TO_CHAR (record_created_date, 'MM/DD/YYYY HH24'),
                     'MM/DD/YYYY HH24'))
         + 1 / 24;


COMMENT ON MATERIALIZED VIEW ADR.HL7_STATS_MV IS 'snapshot table for snapshot ADR.HL7_STATS_MV';

CREATE OR REPLACE PUBLIC SYNONYM HL7_STATS_MV FOR ADR.HL7_STATS_MV;

CREATE INDEX ADR.HL7_STATS_MV_NU_I ON ADR.HL7_STATS_MV
(MESSAGE_TRANSMISSION_TYPE_ID, STD_INSTITUTION_ID, RECORD_CREATED_DATE)
  TABLESPACE M_INDEX_02_P01
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
  STORAGE    (
              BUFFER_POOL      DEFAULT
              FLASH_CACHE      DEFAULT
              CELL_FLASH_CACHE DEFAULT
             )
LOCAL (  
  PARTITION HL7STATSMV_P01
    LOGGING
    NOCOMPRESS 
    TABLESPACE M_INDEX_02_P01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                MAXSIZE          UNLIMITED
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               ),  
  PARTITION
    LOGGING
    NOCOMPRESS 
    TABLESPACE M_INDEX_02_P01
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128M
                NEXT             128M
                MAXSIZE          UNLIMITED
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
                FLASH_CACHE      DEFAULT
                CELL_FLASH_CACHE DEFAULT
               )
)
NOPARALLEL;

GRANT SELECT ON ADR.HL7_STATS_MV TO ADR_R;

GRANT SELECT ON ADR.HL7_STATS_MV TO ADR_RW;
