The following SP removes records from table A if they exist in table
B.
Table A - TEST.TABLE_X has 30 million records
Table B - TEST.TABLE_X_DELTA has 5 million records.
Appx 4 million records shoud be deleted in Table A. However my query
moves the cursor one record at a time. So this is extremely slow. The
reason I have to use a SP is bec I need to avoid fulling the log
space. Thus I need to commit frequently.
I will appreciate if you can guide me in the right direction.
Thanks a bunch!
DECLARE VAR_CIF_ID DECIMAL(9, 0);
DECLARE TABLE_X_DELTA_CRSR CURSOR WITH HOLD FOR
SELECT B.TABLE_X_CIF_ID
FROM TEST.TABLE_X_DELTA B, TEST.TABLE_X A
WHERE A.TABLE_X_AR_ID=B.TABLE_X_AR_ID
AND A.TABLE_X_CIF_ID=B.TABLE_X_CIF_ID;
OPEN TABLE_X_DELTA_CRSR;
LOOP_TABLE_X_DELTA : LOOP
SET VAR_CIF_ID = NULL;
FETCH TABLE_X_DELTA_CRSR INTO VAR_CIF_ID;
IF VAR_CIF_ID IS NULL THEN LEAVE LOOP_TABLE_X_DELTA; END IF;
DELETE FROM TEST.TABLE_X WHERE TABLE_X_CIF_ID = VAR_CIF_ID;
COMMIT;
END LOOP LOOP_TABLE_X_DELTA;
CLOSE TABLE_X_DELTA_CRSR;
END;