Hello everyone,
I have the following relationship between two tables.
The tables are partitioned by a date column on a monthly basis.
I need to generate a report with two sheets, but the execution time is significantly high.
Below, I have provided the two queries.
-- query 1
WITH concatenated_countries AS (
SELECT 'AT,BE,BG,CY,CZ,DE,DK,EE,EL,ES,FI,FR,HR,HU,IE,IT,LT,LU,LV,MT,NL,PL,PT,RO,SE,SI,SK,XI' str
FROM dual
),
all_countries AS (
SELECT regexp_substr(
str,
'[^,]+',
1,
level
) country
FROM concatenated_countries
CONNECT BY level <= length(str) - length(replace(str, ',')) + 1
)
SELECT mrn,
CASE WHEN sender = 'EL' THEN 'GR' ELSE sender END AS "MS_OFFICE_OF_EXPORT",
CASE WHEN receiver = 'EL' THEN 'GR' ELSE receiver END AS "MS_OFFICE_OF_EXIT",
state,
to_char(min_date, 'yyyyMM') AS "YearMonth",
pattern
FROM (
SELECT msg.mrn,
mov.sender,
mov.receiver,
mov.state,
mov.min_date,
listagg(REGEXP_REPLACE(msg_type, '-MSG.[A-Z]+', '') ||
'-' || msg.sender || '-' || msg.recipient || '-' ||
CASE WHEN msg.msg_size > 400 THEN 'over' ELSE 'under' END,
'_' ON OVERFLOW TRUNCATE '...')
WITHIN GROUP (ORDER BY m_min_date, msg_type ASC) AS pattern
FROM movement mov
INNER JOIN msg_exc_rec msg ON (
mov.mrn = msg.mrn
AND mov.env = msg.env
AND mov.domain = msg.domain
)
INNER JOIN all_countries ac ON (1=1)
WHERE mov.domain = 'ECS'
AND mov.sender = ac.COUNTRY
AND mov.receiver NOT IN (ac.COUNTRY, 'OL')
AND msg.is_ieca = 0
AND mov.MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND mov.MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND MOV.MRN NOT IN (
SELECT msg2.MRN
FROM MSG_EXC_REC msg2
WHERE msg.DOMAIN = msg2.DOMAIN
AND msg.ENV = msg2.ENV
AND msg.MRN = msg2.MRN
AND (
(msg2.MSG_TYPE IN ('CD524C-MSG.ECS', 'CD524B-MSG.ECS')
AND msg2.SENDER = ac.COUNTRY)
OR (msg2.MSG_TYPE IN ('CD510A-MSG.ECS', 'CD510C-MSG.ECS')
AND msg2.SENDER = ac.COUNTRY)
OR (msg2.MSG_TYPE IN ('CD518C-MSG.ECS', 'CD518B-MSG.ECS')
AND msg2.RECIPIENT = ac.COUNTRY)
OR (msg2.MSG_TYPE IN ('CD901B-MSG.ECS')
AND msg2.RECIPIENT = ac.COUNTRY)
)
AND msg2.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND msg2.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND msg2.is_ieca = 0
)
AND MOV.MRN IN (
SELECT msg3.MRN
FROM MSG_EXC_REC msg3
WHERE msg.DOMAIN = msg3.DOMAIN
AND msg.ENV = msg3.ENV
AND msg.MRN = msg3.MRN
AND (
(msg3.MSG_TYPE IN ('CD501B-MSG.ECS', 'CD501C-MSG.ECS')
AND msg3.SENDER = ac.COUNTRY)
OR (msg3.MSG_TYPE IN ('CD503B-MSG.ECS', 'CD503C-MSG.ECS')
AND msg3.SENDER = ac.COUNTRY
AND msg3.MSG_SIZE > 400)
)
AND msg3.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND msg3.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND msg3.is_ieca = 0
)
GROUP BY msg.mrn, mov.sender, mov.receiver, mov.state, mov.min_date
)
ORDER BY mrn
FETCH FIRST 1048575 ROWS ONLY;
-- query 2
WITH concatenated_countries AS (
SELECT 'AT,BE,BG,CY,CZ,DE,DK,EE,EL,ES,FI,FR,HR,HU,IE,IT,LT,LU,LV,MT,NL,PL,PT,RO,SE,SI,SK,XI' str
FROM dual
),
all_countries AS (
SELECT regexp_substr(
str,
'[^,]+',
1,
level
) country
FROM concatenated_countries
CONNECT BY level <= length(str) - length(replace(str, ',')) + 1
)
SELECT mrn,
CASE WHEN sender = 'EL' THEN 'GR' ELSE sender END AS "MS_OFFICE_OF_EXPORT",
CASE WHEN receiver = 'EL' THEN 'GR' ELSE receiver END AS "MS_OFFICE_OF_EXIT",
state,
to_char(min_date, 'yyyyMM') AS "YearMonth",
pattern
FROM (
SELECT msg.mrn,
mov.sender,
mov.receiver,
mov.state,
mov.min_date,
listagg(REGEXP_REPLACE(msg_type, '-MSG.[A-Z]+', '') ||
'-' || msg.sender || '-' || msg.recipient || '-' ||
CASE WHEN msg.msg_size > 400 THEN 'over' ELSE 'under' END,
'_' ON OVERFLOW TRUNCATE '...')
WITHIN GROUP (ORDER BY m_min_date, msg_type ASC) AS pattern
FROM movement mov
INNER JOIN msg_exc_rec msg ON (
mov.mrn = msg.mrn
AND mov.env = msg.env
AND mov.domain = msg.domain
)
INNER JOIN all_countries ac ON (1=1)
WHERE mov.domain = 'ECS'
AND mov.receiver = ac.COUNTRY
AND mov.sender NOT IN (ac.COUNTRY, 'OL')
AND msg.is_ieca = 0
AND mov.MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND mov.MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND MOV.MRN NOT IN (
SELECT msg2.MRN
FROM MSG_EXC_REC msg2
WHERE msg.DOMAIN = msg2.DOMAIN
AND msg.ENV = msg2.ENV
AND msg.MRN = msg2.MRN
AND (
(msg2.MSG_TYPE IN ('CD524C-MSG.ECS', 'CD524B-MSG.ECS')
AND msg2.RECIPIENT = ac.COUNTRY)
OR (msg2.MSG_TYPE IN ('CD510A-MSG.ECS', 'CD510C-MSG.ECS')
AND msg2.RECIPIENT = ac.COUNTRY)
OR (msg2.MSG_TYPE IN ('CD518C-MSG.ECS', 'CD518B-MSG.ECS')
AND msg2.SENDER = ac.COUNTRY)
)
AND msg2.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND msg2.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND msg2.is_ieca = 0
)
AND MOV.MRN IN (
SELECT msg3.MRN
FROM MSG_EXC_REC msg3
WHERE msg.DOMAIN = msg3.DOMAIN
AND msg.ENV = msg3.ENV
AND msg.MRN = msg3.MRN
AND (
(msg3.MSG_TYPE IN ('CD501B-MSG.ECS', 'CD501C-MSG.ECS')
AND msg3.RECIPIENT = ac.COUNTRY)
OR (msg3.MSG_TYPE IN ('CD503B-MSG.ECS', 'CD503C-MSG.ECS')
AND msg3.RECIPIENT = ac.COUNTRY
AND msg3.MSG_SIZE > 400)
)
AND msg3.M_MIN_DATE < TO_DATE('20250101 000000', 'yyyyMMdd HH24miss')
AND msg3.M_MIN_DATE >= TO_DATE('20240601 000000', 'yyyyMMdd HH24miss')
AND msg3.is_ieca = 0
)
GROUP BY msg.mrn, mov.sender, mov.receiver, mov.state, mov.min_date
)
ORDER BY mrn
FETCH FIRST 1048575 ROWS ONLY;
The execution duration for each query is
query 1 --> 56:50 MIN:SEC
query 2 --> 10:12:01 HH:MIN:SEC
The execution plans are as below
query 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_012"."rowlimit_$$_rownumber"<=1048575)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "MSG"."MRN")<=1048575)
7 - access("MOV"."SENDER"="AC"."COUNTRY")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("MOV"."RECEIVER"<>"AC"."COUNTRY")
9 - filter(LEVEL<=28)
12 - filter("MOV"."MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MOV"."DOMAIN"='ECS' AND "MOV"."RECEIVER"<>'OL' AND
"MOV"."MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
13 - filter("MSG"."IS_IECA"=0)
14 - access("MOV"."MRN"="MSG"."MRN" AND "MSG"."DOMAIN"='ECS' AND "MOV"."ENV"="MSG"."ENV")
filter( NOT EXISTS (SELECT 0 FROM "MSG_EXC_REC" "MSG2" WHERE :B1=:B2 AND "MSG2"."ENV"=:B3 AND "MSG2"."DOMAIN"=:B4 AND "MSG2"."MRN"=:B5
AND "MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."RECIPIENT"=:B6 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."SENDER"=:B7 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR
"MSG2"."SENDER"=:B8 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS') OR "MSG2"."MSG_TYPE"='CD901B-MSG.ECS' AND
"MSG2"."RECIPIENT"=:B9) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15 - filter(:B1=:B2)
16 - filter("MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."RECIPIENT"=:B1 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."SENDER"=:B2 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR
"MSG2"."SENDER"=:B3 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS') OR "MSG2"."MSG_TYPE"='CD901B-MSG.ECS' AND
"MSG2"."RECIPIENT"=:B4) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
17 - access("MSG2"."MRN"=:B1 AND "MSG2"."DOMAIN"=:B2 AND "MSG2"."ENV"=:B3)
18 - filter("MSG3"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MSG3"."SENDER"="AC"."COUNTRY" AND
(("MSG3"."MSG_TYPE"='CD501B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD501C-MSG.ECS') OR "MSG3"."MSG_SIZE">400 AND ("MSG3"."MSG_TYPE"='CD503B-MSG.ECS'
OR "MSG3"."MSG_TYPE"='CD503C-MSG.ECS')) AND "MSG3"."IS_IECA"=0 AND "MSG3"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
19 - access("MSG"."MRN"="MSG3"."MRN" AND "MSG3"."DOMAIN"='ECS' AND "MSG"."ENV"="MSG3"."ENV")
filter("MOV"."MRN"="MSG3"."MRN" AND "MSG"."DOMAIN"="MSG3"."DOMAIN")
55 rows selected.
query 2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_012"."rowlimit_$$_rownumber"<=1048575)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 - filter(ROW_NUMBER() OVER ( ORDER BY "MSG"."MRN")<=1048575)
10 - filter(LEVEL<=28)
13 - filter("MSG3"."DOMAIN"='ECS' AND (("MSG3"."MSG_TYPE"='CD501B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD501C-MSG.ECS') OR "MSG3"."MSG_SIZE">400
AND ("MSG3"."MSG_TYPE"='CD503B-MSG.ECS' OR "MSG3"."MSG_TYPE"='CD503C-MSG.ECS')) AND "MSG3"."IS_IECA"=0)
14 - access("MSG3"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MSG3"."RECIPIENT"="AC"."COUNTRY" AND "MSG3"."M_MIN_DATE"<TIMESTAMP'
2025-01-01 00:00:00')
filter("MSG3"."RECIPIENT"="AC"."COUNTRY")
15 - filter("MOV"."MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND "MOV"."RECEIVER"="AC"."COUNTRY" AND "MOV"."SENDER"<>"AC"."COUNTRY" AND
"MOV"."SENDER"<>'OL' AND "MOV"."MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
16 - access("MOV"."MRN"="MSG3"."MRN" AND "MOV"."DOMAIN"='ECS')
17 - access("MSG"."MRN"="MSG3"."MRN" AND "MSG"."DOMAIN"='ECS' AND "MSG"."ENV"="MSG3"."ENV")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
filter("MOV"."MRN"="MSG"."MRN" AND "MOV"."ENV"="MSG"."ENV" AND "MSG"."DOMAIN"="MSG3"."DOMAIN" AND NOT EXISTS (SELECT 0 FROM
"MSG_EXC_REC" "MSG2" WHERE :B1=:B2 AND "MSG2"."ENV"=:B3 AND "MSG2"."DOMAIN"=:B4 AND "MSG2"."MRN"=:B5 AND "MSG2"."M_MIN_DATE">=TIMESTAMP'
2024-06-01 00:00:00' AND ("MSG2"."SENDER"=:B6 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR
"MSG2"."RECIPIENT"=:B7 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS') OR "MSG2"."RECIPIENT"=:B8 AND
("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS')) AND "MSG2"."IS_IECA"=0 AND "MSG2"."M_MIN_DATE"<TIMESTAMP'
2025-01-01 00:00:00'))
18 - filter(:B1=:B2)
19 - filter("MSG2"."M_MIN_DATE">=TIMESTAMP' 2024-06-01 00:00:00' AND ("MSG2"."SENDER"=:B1 AND ("MSG2"."MSG_TYPE"='CD518B-MSG.ECS' OR
"MSG2"."MSG_TYPE"='CD518C-MSG.ECS') OR "MSG2"."RECIPIENT"=:B2 AND ("MSG2"."MSG_TYPE"='CD524B-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD524C-MSG.ECS')
OR "MSG2"."RECIPIENT"=:B3 AND ("MSG2"."MSG_TYPE"='CD510A-MSG.ECS' OR "MSG2"."MSG_TYPE"='CD510C-MSG.ECS')) AND "MSG2"."IS_IECA"=0 AND
"MSG2"."M_MIN_DATE"<TIMESTAMP' 2025-01-01 00:00:00')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20 - access("MSG2"."MRN"=:B1 AND "MSG2"."DOMAIN"=:B2 AND "MSG2"."ENV"=:B3)
21 - filter("MSG"."IS_IECA"=0)
57 rows selected.
As you can see, the execution plans for the first and second queries are different.
Could you suggest any ways to improve performance?
I was considering creating two materialized views—one for each sheet—but I believe 10 hours should be sufficient processing time.
Below, I have also listed the relevant indexes (displayed in execution plans):
IDX_MSG_EXC_MMINDATE_SEN_REC →
CREATE INDEX "IDX_MSG_EXC_MMINDATE_SEN_REC" ON "MSG_EXC_REC" ("M_MIN_DATE", "SENDER", "RECIPIENT");
IDX_MOVEMENT_MRN →
CREATE UNIQUE INDEX "IDX_MOVEMENT_MRN" ON "MOVEMENT" ("MRN", "DOMAIN", "ENV", "CRN");
IDX_MSG_EXC_REC_MRN_DOMAIN_ENV →
CREATE INDEX "IDX_MSG_EXC_REC_MRN_DOMAIN_ENV" ON "MSG_EXC_REC" ("MRN", "DOMAIN", "ENV");