Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MV performance refresh issue

BufossJun 22 2025

Hi all,
I have a materialized view that links errors with messages in an application.
All messages are stored in the MSG_EXC_REC table, while the ERROR_MESSAGE table contains information about error messages received from submitted XML files.

The goal is to retrieve all error message information, and—when available—also include the original rejected message.

All the error info + details (sended from xml) can be found with the below query:

SELECT MDI.ID, REPLACE(MDI.DOMAIN, 'AES', 'ECS') AS DOMAIN, MDI.PHASE, MDI.MSG_TYPE,
  MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE,
  SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)) AS SEND_C_CODE,
  SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)) AS REC_C_CODE,
  MDI.MSG_ID, MD906.ERROR_TYPE AS ERROR_TYPE, MD906.REASON AS ERROR_REASON,
  MD906.POINTER AS ERROR_POINTER, MD906.ID AS ERROR_ID,
  NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
FROM ERROR_MESSAGE MDI
    INNER JOIN ERROR_MESSAGE_906 MD906 ON MDI.ID = MD906.EM_ID
WHERE NOT EXISTS (SELECT 1 FROM EXCLUDED_MESSAGES EM WHERE EM.MSG_ID = MDI.MSG_ID)
UNION ALL
SELECT MDI.ID, REPLACE(MDI.DOMAIN, 'AES', 'ECS') AS DOMAIN, MDI.PHASE, MDI.MSG_TYPE,
   MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE,
   SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)) AS SEND_C_CODE,
   SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)) AS REC_C_CODE,
   MDI.MSG_ID, MD907.ERROR_CODE_IN AS ERROR_TYPE, NULL AS ERROR_REASON,
   NULL AS ERROR_POINTER, MD907.ID AS ERROR_ID,
   NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
FROM ERROR_MESSAGE MDI
   INNER JOIN ERROR_MESSAGE_907 MD907 ON MDI.ID = MD907.EM_ID
WHERE NOT EXISTS (SELECT 1 FROM EXCLUDED_MESSAGES EM WHERE EM.MSG_ID = MDI.MSG_ID)
UNION ALL
SELECT MDI.ID, REPLACE(MDI.DOMAIN, 'AES', 'ECS') AS DOMAIN, MDI.PHASE, MDI.MSG_TYPE,
   MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE,
   SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)) AS SEND_C_CODE,
   SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)) AS REC_C_CODE,
   MDI.MSG_ID, MD917.ERROR_CODE AS ERROR_TYPE, MD917.REASON AS ERROR_REASON,
   NULL AS ERROR_POINTER, MD917.ID AS ERROR_ID,
   NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
FROM ERROR_MESSAGE MDI
   INNER JOIN ERROR_MESSAGE_917 MD917 ON MDI.ID = MD917.EM_ID
WHERE NOT EXISTS (SELECT 1 FROM EXCLUDED_MESSAGES EM WHERE EM.MSG_ID = MDI.MSG_ID)
UNION ALL
SELECT MDI.ID, REPLACE(MDI.DOMAIN, 'AES', 'ECS') AS DOMAIN, MDI.PHASE, MDI.MSG_TYPE,
   MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE,
   SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)) AS SEND_C_CODE,
   SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)) AS REC_C_CODE,
   MDI.MSG_ID, MD056.ERROR_CODE AS ERROR_TYPE,
   MD056.REASON AS ERROR_REASON, MD056.POINTER AS ERROR_POINTER, MD056.ID AS ERROR_ID,
   MDI.BUSINESS_REJECTION_TYPE, MDI.ENS_MRN, MDI.CORREL_ID
FROM ERROR_MESSAGE MDI
   INNER JOIN ERROR_MESSAGE_056 MD056 ON MDI.ID = MD056.EM_ID
WHERE NOT EXISTS (SELECT 1 FROM EXCLUDED_MESSAGES EM WHERE EM.MSG_ID = MDI.MSG_ID);

Based on the error information, I want to retrieve one record from the MSG_EXC_REC table that satisfies the following criteria:

  1. MSG_EXC_REC.DOMAIN = ERROR_DETAILS.DOMAIN
  2. MSG_EXC_REC.MSG_ID = ERROR_DETAILS.MSG_ID
  3. MSG_EXC_REC.MSG_TYPE = ERROR_DETAILS.MSG_TYPE
  4. NVL(MSG_EXC_REC.CORREL_ID, '') = NVL(ERROR_DETAILS.CORREL_ID, '')
  5. ERROR_DETAILS.MSG_REC_DATE < MSG_EXC_REC.M_MIN_DATE
  6. ERROR_DETAILS.MSG_REC_DATE is between MER.M_MIN_DATE and MER.M_MIN_DATE + INTERVAL '5' HOUR
  7. MSG_EXC_REC.M_MIN_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -48), 'MM')

If one or more records from MSG_EXC_REC match based on the same DOMAIN, ENV, MSG_ID, CORREL_ID (if present), and MSG_TYPE, then the matching record should be the one from MSG_EXC_REC with the maximum M_MIN_DATE,
where ERROR_DETAILS.MSG_REC_DATE >= MSG_EXC_REC.M_MIN_DATE.

After identifying this record, I want to perform a left join to check whether the original rejected message exists, based on the selected MSG_EXC_REC record.

The matching criteria (using MER as the alias for the error message and ORIGINAL_MER as the alias for the original rejected message from MSG_EXC_REC) are as follows:

  1. MER.CORREL_ID = ORIGINAL_MER.MSG_ID
  2. MER.DOMAIN = ORIGINAL_MER.DOMAIN
  3. MER.M_MIN_DATE >= ORIGINAL_MER.M_MIN_DATE

So, my materialized view is

CREATE MATERIALIZED VIEW MV_ERROR_MESSAGES
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
WITH TMPUNION AS (
SELECT MDI.ID, replace(MDI.DOMAIN, 'AES', 'ECS') AS DOMAIN, MDI.PHASE, MDI.MSG_TYPE, MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE,
      SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)) AS SEND_C_CODE,
      SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)) AS REC_C_CODE,
      MDI.MSG_ID, MD906.ERROR_TYPE AS ERROR_TYPE, MD906.REASON AS ERROR_REASON, MD906.POINTER AS ERROR_POINTER, MD906.ID AS ERROR_ID,
      NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
 FROM ERROR_MESSAGE MDI
      INNER JOIN ERROR_MESSAGE_906 MD906 ON MDI.ID = MD906.EM_ID
UNION ALL
SELECT MDI.ID, replace(MDI.DOMAIN, 'AES', 'ECS'), MDI.PHASE, MDI.MSG_TYPE, MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, 
      SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)),
      SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)),
      MDI.MSG_ID, MD907.ERROR_CODE_IN AS ERROR_TYPE, NULL AS ERROR_REASON, NULL AS ERROR_POINTER, MD907.ID AS ERROR_ID,
      NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
 FROM ERROR_MESSAGE MDI
      INNER JOIN ERROR_MESSAGE_907 MD907 ON MDI.ID = MD907.EM_ID
UNION ALL
SELECT MDI.ID, replace(MDI.DOMAIN, 'AES', 'ECS'), MDI.PHASE, MDI.MSG_TYPE,MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, 
      SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)),
      SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)),
      MDI.MSG_ID, MD917.ERROR_CODE AS ERROR_TYPE, MD917.REASON AS ERROR_REASON, NULL AS ERROR_POINTER, MD917.ID AS ERROR_ID,
      NULL AS BUSINESS_REJECTION_TYPE, NULL AS ENS_MRN, MDI.CORREL_ID
 FROM ERROR_MESSAGE MDI
      INNER JOIN ERROR_MESSAGE_917 MD917 ON MDI.ID = MD917.EM_ID
UNION ALL
SELECT MDI.ID, replace(MDI.DOMAIN, 'AES', 'ECS'), MDI.PHASE, MDI.MSG_TYPE,MDI.SIZE_CATEGORY, MDI.IS_ZIP, MDI.MSG_REC_DATE, 
      SUBSTR(MDI.SEND_C_CODE, -least(length(MDI.SEND_C_CODE), 2)),
      SUBSTR(MDI.REC_C_CODE, -least(length(MDI.REC_C_CODE), 2)),
      MDI.MSG_ID, MD056.ERROR_CODE AS ERROR_TYPE, MD056.REASON AS ERROR_REASON, MD056.POINTER AS ERROR_POINTER, MD056.ID AS ERROR_ID,
      MDI.BUSINESS_REJECTION_TYPE, MDI.ENS_MRN, MDI.CORREL_ID
 FROM ERROR_MESSAGE MDI
      INNER JOIN ERROR_MESSAGE_056 MD056 ON MDI.ID = MD056.EM_ID
),
SELECT TMPUNION.ID AS EM_ID
    , TMPUNION.DOMAIN
    , TMPUNION.PHASE
    , SUBSTR(TMPUNION.MSG_TYPE, 0, 6) AS MSG_TYPE
    , TMPUNION.SIZE_CATEGORY
    , TMPUNION.IS_ZIP
    , TMPUNION.MSG_REC_DATE
    , TMPUNION.SEND_C_CODE
    , TMPUNION.REC_C_CODE
     , MSG.CCN_UNIQUE_ID
    , TMPUNION.ERROR_TYPE
    , TMPUNION.ERROR_REASON
    , TMPUNION.ERROR_POINTER
    , MSG.ENV
    , MSG.REJECTED_MESSAGE_TYPE
    , MSG.REJECTED_MESSAGE_SENDER
    , MSG.REJECTED_MESSAGE_RECIPIENT
     , MSG.REJECTED_ID
    , CASE
        WHEN (TMPUNION.MSG_TYPE LIKE 'CD906D%' OR TMPUNION.MSG_TYPE LIKE 'CD917D%' OR TMPUNION.MSG_TYPE LIKE 'CD056D%') THEN 'IF'
        WHEN TMPUNION.MSG_TYPE LIKE 'CD9%' AND MSG.RECIPIENT_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'EX' --Exogen
        WHEN TMPUNION.ERROR_REASON LIKE 'NCAv%' THEN 'EX' --Exogen
        WHEN TMPUNION.MSG_TYPE LIKE 'CD917%' AND MSG.SENDER_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'ET' --Endogen ToBe
        WHEN TMPUNION.ERROR_REASON = 'ieCAvB' THEN 'ET' --Endogen ToBe
        WHEN TMPUNION.MSG_TYPE LIKE 'CD907%' AND MSG.SENDER_GW IN ('XXIP1','XXIP2','XXIP4','XXIP5','XXIP7','XXIP9','XXIB1','XXIB2','XXIB5','XXIB7') THEN 'EL' --Endogen Legacy
        WHEN TMPUNION.ERROR_REASON = 'ieCAvC' THEN 'EL' --Endogen Legacy
             ELSE 'IF' --No conversion
END AS IECA_STATUS
    , TMPUNION.ERROR_ID AS ERROR_ID
    , MSG.IS_IECA
    , TMPUNION.BUSINESS_REJECTION_TYPE
    , TMPUNION.ENS_MRN
FROM TMPUNION
cross apply (
   select CCN_UNIQUE_ID, ENV, REJECTED_ID, RECIPIENT_GW, SENDER_GW, IS_IECA
        , REJECTED_MESSAGE_TYPE, REJECTED_MESSAGE_SENDER, REJECTED_MESSAGE_RECIPIENT
        , ROW_NUM
   from (
       select MER.CCN_UNIQUE_ID, MER.ENV, MER.CORREL_ID as REJECTED_ID, MER.RECIPIENT_GW, MER.SENDER_GW, MER.IS_IECA
           , SUBSTR(ORIGINAL_MER.MSG_TYPE, 0, 6) AS REJECTED_MESSAGE_TYPE, ORIGINAL_MER.SENDER AS REJECTED_MESSAGE_SENDER
           , ORIGINAL_MER.RECIPIENT AS REJECTED_MESSAGE_RECIPIENT
           , ROW_NUMBER()
             OVER (partition by MER.DOMAIN, MER.ENV, MER.MSG_TYPE, MER.MSG_ID  order by MER.M_MIN_DATE desc) as ROW_NUM
       from MSG_EXC_REC MER
            LEFT JOIN MSG_EXC_REC ORIGINAL_MER ON MER.CORREL_ID = ORIGINAL_MER.MSG_ID
                                              AND MER.DOMAIN = ORIGINAL_MER.DOMAIN
                                              AND MER.M_MIN_DATE >= ORIGINAL_MER.M_MIN_DATE
       where TMPUNION.MSG_ID = MER.MSG_ID
         AND TMPUNION.DOMAIN = MER.DOMAIN
         AND NVL(TMPUNION.CORREL_ID, '~') = NVL(MER.CORREL_ID, '~')
         AND TMPUNION.MSG_TYPE = MER.MSG_TYPE
         AND TMPUNION.MSG_REC_DATE BETWEEN MER.M_MIN_DATE
                                       AND MER.M_MIN_DATE + interval '5' hour
         AND MER.M_MIN_DATE >= TRUNC(ADD_MONTHS(SYSDATE, -48), 'MM') -- Limit to 4 years of data
   )
   where ROW_NUM=1
) MSG;

The execution plan cost is approximately 40 million, and the materialized view refresh takes around 5 hours (old_explain_plan.sql).
The MSG_EXC_REC table is partitioned by month on the M_MIN_DATE field.

question 1 : Do you have any suggestions/hints to improve performance? The current refresh duration is not sustainable.
question 2 : Would it be beneficial to split the UNION part into a separate materialized view?
question 3 : Can be applied LATERAL operator or the performance will be worst ?

Thanks in advance

Comments
Post Details
Added on Jun 22 2025
1 comment
133 views