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:
MSG_EXC_REC.DOMAIN = ERROR_DETAILS.DOMAIN
MSG_EXC_REC.MSG_ID = ERROR_DETAILS.MSG_ID
MSG_EXC_REC.MSG_TYPE = ERROR_DETAILS.MSG_TYPE
NVL(MSG_EXC_REC.CORREL_ID, '') = NVL(ERROR_DETAILS.CORREL_ID, '')
ERROR_DETAILS.MSG_REC_DATE < MSG_EXC_REC.M_MIN_DATE
ERROR_DETAILS.MSG_REC_DATE
is between MER.M_MIN_DATE
and MER.M_MIN_DATE + INTERVAL '5' HOUR
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:
MER.CORREL_ID = ORIGINAL_MER.MSG_ID
MER.DOMAIN = ORIGINAL_MER.DOMAIN
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