Hi all,
I need some assistance in optimizing the performance of a query.
To provide some context, I have two tables structured in a MASTER-DETAIL relationship.
The first table is named MOVEMENT, and the second one is MOVEMENT_MESSAGE.
These tables are linked through the relationship MOVEMENT.ID = MOVEMENT_MESSAGE.MOVEMENT_ID.
Basic structure of each table

Additionally, the MOVEMENT table is partitioned by month using the following partitioning scheme:
PARTITION BY RANGE ("MIN_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
Furthermore, there are the following indexes
CREATE INDEX "IDX_MOV_STATE" ON "CSMIS2_USER"."MOVEMENT" ("STATE", "MIN_DATE", "MAX_DATE");
CREATE INDEX "IDX_MOVEMENT_MSG_ID" ON "CSMIS2_USER"."MOVEMENT_MESSAGE" ("MOVEMENT_ID") ;
I have the below query in order to check based on a state of movement and state_modification_date if a number of days is exceeded.
SELECT
mov_send,
mov_rec,
domain,
mrn,
state,
LISTAGG(SUBSTR(msg_type, 3, 4), '') WITHIN GROUP (ORDER BY SEND_DATE) msg_list,
LISTAGG(msg_send, '') WITHIN GROUP (ORDER BY SEND_DATE) send_list,
LISTAGG(msg_rec, '') WITHIN GROUP (ORDER BY SEND_DATE) rcv_list,
min_date,
max_date,
EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) AS days_stuck
FROM (
SELECT
a.SENDER mov_send,
a.RECEIVER mov_rec,
b.MSG_TYPE msg_type,
b.SENDER msg_send,
b.RECEIVER msg_rec,
a.MRN mrn,
b.SEND_DATE,
a.domain,
a.state,
a.min_date,
a.max_date,
a.state_modification_date
FROM MOVEMENT a
INNER JOIN MOVEMENT_MESSAGE b ON a.ID = b.MOVEMENT_ID
WHERE a.ID IN (
SELECT id
FROM movement
WHERE min_date >= SYSDATE - 180
AND min_date <= SYSDATE
AND (
(domain = 'NCTS' AND (
(state_modification_date IS NOT NULL AND state = 'Initial' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :1) OR
(state_modification_date IS NOT NULL AND state = 'Incidentregistered' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :2) OR
(state_modification_date IS NOT NULL AND state = 'Movementleftsecurityarea' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :3) OR
(state_modification_date IS NOT NULL AND state = 'Notificationcrossingfrontier' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :4) OR
(state_modification_date IS NOT NULL AND state = 'Unknown' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :5) OR
(state_modification_date IS NOT NULL AND state = 'Underenquiryprocedure' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :6) OR
(state_modification_date IS NOT NULL AND state = 'Released' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :7) OR
(state_modification_date IS NOT NULL AND state = 'Recoveryrecommended' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :8) OR
(state_modification_date IS NOT NULL AND state = 'Arrived' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :9) OR
(state_modification_date IS NOT NULL AND state = 'Underrecoveryprocedure' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :10)
))
OR
(domain = 'ECS' AND (
(state_modification_date IS NOT NULL AND state = 'Initial' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :11) OR
(state_modification_date IS NOT NULL AND state = 'UnderExitConfirmationRequest' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :12) OR
(state_modification_date IS NOT NULL AND state = 'AwaitingforPCOControlDecision' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :13) OR
(state_modification_date IS NOT NULL AND state = 'ReleasedforExport' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :14) OR
(state_modification_date IS NOT NULL AND state = 'Unknown' AND EXTRACT(DAY FROM SYSTIMESTAMP - state_modification_date) >= :15)
))
)
)
AND IS_IECA = 0
AND ENV = 'OP'
)
GROUP BY mov_send, mov_rec, mrn, domain, state, min_date, max_date, state_modification_date;
, where :1 until :15 are configuration binding variables.
This query takes about 406 seconds to be executed, so because the transaction timeout is set to 5 minutes, it fails.
Attached you can find the execution plan explain_plan.txt
Is there any way to improve the query ?
Additionally, if we assume that all parameters have the same value (let's say 3), can the restructuring process be simplified?
Thanks in advance for your time