SQL & PL/SQL

Hello everyone,
I have two BIG tables with names MSG_EXC_REC and MOVEMENT, and the relation is that multiple records of MSG_EXC_REC create a row of MOVEMENT using key MRN, DOMAIN and ENV .

Both tables are partitioned.

MSG_EXC_REC is partitioned as below

 PARTITION BY RANGE ("M_MIN_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')
 PARTITION "BEFORE_2010"  VALUES LESS THAN (TIMESTAMP' 2010-01-01 00:00:00')

MOVEMENT is partitioned as below

 PARTITION BY RANGE ("MIN_DATE") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
 PARTITION "BEFORE_2010"  VALUES LESS THAN (TIMESTAMP' 2010-01-01 00:00:00')

On table MSG_EXC_REC there is a column IS_IECA NUMBER(1,0) and for performance reason I want to migrate this column and add it to the table MOVEMENT.
The Movement.is_ieca column will be set to 1 if and only if exists a message for the same combination (domain,mrn,env) in MSG_EXC_REC with value is_ieca=1.
It's worth to notice that the MOVEMENT.IS_IECA has been initiated with 0 (default 0).

Below you can find my actions to do the migration with plsql resulting in 1915sec --> 00:31:55 (per semester)

CREATE TABLE MIGRATE_IECA_ERROR_LOG (
ERROR_ID      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ERROR_MESSAGE VARCHAR2(4000),
ERROR_DATE    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DECLARE
 -- Create date ranges of month
 CURSOR get_ranges_cur(p_start_date TIMESTAMP)
 IS
 select add_months ( p_start_date,  level - 1 ) as start_date
      , add_months ( p_start_date,  level ) as end_date
   from dual  
connect by level <= months_between ( TRUNC(SYSDATE), p_start_date) + 1
  order by start_date desc;
 -- Get the distinct triplets for a period
 CURSOR movement_triplets_cur(p_start_date TIMESTAMP, p_end_date TIMESTAMP)
 IS
 SELECT ID, MRN, DOMAIN, ENV
   FROM MOVEMENT
  WHERE DOMAIN IN ('NCTS', 'ECS')
    AND MIN_DATE >= p_start_date
    AND MIN_DATE < p_end_date;
 TYPE movements_t IS TABLE OF movement_triplets_cur%ROWTYPE INDEX BY PLS_INTEGER;
 l_movements_data   movements_t;
 v_limit        PLS_INTEGER := 2000;
 v_start_date   TIMESTAMP := TO_TIMESTAMP('01/01/2024 00:00:00', 'DD/MM/YYYY HH24:MI:SS');
 l_error_count  NUMBER := 0;
 -- --------------
 -- Logging errors
 PROCEDURE migrate_ieca_error_log(p_error_msg VARCHAR2)
 IS
   PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
   INSERT INTO migrate_ieca_error_log (error_message, error_date)
   VALUES (p_error_msg, SYSDATE);
   COMMIT;
 END migrate_ieca_error_log;
 -- --------------
BEGIN
 <<RANGE_LOOP>>
 FOR REC IN get_ranges_cur(v_start_date)
 LOOP
   OPEN movement_triplets_cur(REC.start_date, REC.end_date);
   LOOP
     FETCH movement_triplets_cur
     BULK COLLECT INTO l_movements_data LIMIT v_limit;
     EXIT WHEN l_movements_data.COUNT = 0;
     BEGIN
       FORALL i IN l_movements_data.FIRST..l_movements_data.LAST SAVE EXCEPTIONS
         UPDATE MOVEMENT M
            SET M.IS_IECA = 1
          WHERE exists ( select 1
                           from msg_exc_rec R
                            WHERE R.MRN = l_movements_data(i).MRN
                             AND R.DOMAIN = l_movements_data(i).DOMAIN
                             AND R.ENV = l_movements_data(i).ENV
                             AND R.IS_IECA = 0)
            AND M.ID = l_movements_data(i).ID;
         COMMIT;
     EXCEPTION
       WHEN OTHERS THEN
         l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
         migrate_ieca_error_log('Number of failures: ' || l_error_count);
         FOR i IN 1 .. l_error_count LOOP
           migrate_ieca_error_log('Error: ' || i ||
                                  ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                                  ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
         END LOOP;
     END;
     EXIT WHEN l_movements_data.COUNT = 0;
   END LOOP;
   COMMIT;
   CLOSE movement_triplets_cur;
 END LOOP RANGE_LOOP;
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
   migrate_ieca_error_log('Unhandled Exception: ' || SQLERRM);
   RAISE;
END;
/

and the sql approach resulting in 3786sec→01:03:06

MERGE INTO MOVEMENT M
USING (
 SELECT MRN, DOMAIN, ENV
   FROM MSG_EXC_REC
  WHERE DOMAIN IN ('NCTS', 'ECS')
    AND M_MIN_DATE >= TO_TIMESTAMP('01/01/2024 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
    AND M_MIN_DATE <= TRUNC(SYSDATE)
    AND IS_IECA = 1
  GROUP BY MRN, DOMAIN, ENV
) E
ON (M.MRN = E.MRN AND M.DOMAIN = E.DOMAIN AND M.ENV = E.ENV)
WHEN MATCHED THEN
 UPDATE SET M.IS_IECA = 1
 WHERE M.IS_IECA = 0;
 
commit;

I think that the difference is based on the fact that the plsql block uses the monthly partitions.
Is there any way to do it more efficient ?

I have also indexes on MRN, DOMAIN, ENV in both tables.

Thanks in advance