Need to improve my stored procedure , your comments are welcome!!

[Metin Esat]

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_DE LTA 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_C RSR CURSOR WITH HOLD FOR
SELECT B.TABLE_X_CIF_I D
FROM TEST.TABLE_X_DE LTA B, TEST.TABLE_X A
WHERE A.TABLE_X_AR_ID =B.TABLE_X_AR_I D
AND A.TABLE_X_CIF_I D=B.TABLE_X_CIF _ID;

OPEN TABLE_X_DELTA_C RSR;

LOOP_TABLE_X_DE LTA : LOOP

SET VAR_CIF_ID = NULL;
FETCH TABLE_X_DELTA_C RSR INTO VAR_CIF_ID;

IF VAR_CIF_ID IS NULL THEN LEAVE LOOP_TABLE_X_DE LTA; END IF;

DELETE FROM TEST.TABLE_X WHERE TABLE_X_CIF_ID = VAR_CIF_ID;
COMMIT;

END LOOP LOOP_TABLE_X_DE LTA;

CLOSE TABLE_X_DELTA_C RSR;

END;

[Mark A]

"Metin Esat" <me****@hotmail .com> wrote in message
news:d7******** *************** ***@posting.goo gle.com...

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_DE LTA 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_C RSR CURSOR WITH HOLD FOR
SELECT B.TABLE_X_CIF_I D
FROM TEST.TABLE_X_DE LTA B, TEST.TABLE_X A
WHERE A.TABLE_X_AR_ID =B.TABLE_X_AR_I D
AND A.TABLE_X_CIF_I D=B.TABLE_X_CIF _ID;

OPEN TABLE_X_DELTA_C RSR;

LOOP_TABLE_X_DE LTA : LOOP

SET VAR_CIF_ID = NULL;
FETCH TABLE_X_DELTA_C RSR INTO VAR_CIF_ID;

IF VAR_CIF_ID IS NULL THEN LEAVE LOOP_TABLE_X_DE LTA; END IF;

DELETE FROM TEST.TABLE_X WHERE TABLE_X_CIF_ID = VAR_CIF_ID;
COMMIT;

END LOOP LOOP_TABLE_X_DE LTA;

CLOSE TABLE_X_DELTA_C RSR;

END;

One thing that will help is only commit every 100 - 1000 deletes. You can
set up a counter in your loop and then reset the counter with the commit.
Each commit takes longer than the delete.

I assume you have the proper indexes set up?

[Serge Rielau]

Try this:
DECLARE SQLCODE INTEGER;

LOOP_TABLE_X_DE LTA : LOOP
DELETE FROM
(SELECT 1 FROM TEST.TABLE_X AS A
WHERE EXISTS(SELECT 1
FROM TEST.TABLE_X_DE LTA B
WHERE A.TABLE_X_AR_ID =B.TABLE_X_AR_I D
AND A.TABLE_X_CIF_I D=B.TABLE_X_CIF _ID)
FETCH FIRST 1000 ROWS ONLY) AS D
IF SQLCODE = 100 THEN LEAVE LOOP_TABLE_X_DE LTA; END
COMMIT;
END LOOP;
COMMIT;

Of course you can play with the value in FETCH FIRST.

Cheers
Serge

[Serge Rielau]

Try this:
DECLARE SQLCODE INTEGER;

LOOP_TABLE_X_DE LTA : LOOP
DELETE FROM
(SELECT 1 FROM TEST.TABLE_X AS A
WHERE EXISTS(SELECT 1
FROM TEST.TABLE_X_DE LTA B
WHERE A.TABLE_X_AR_ID =B.TABLE_X_AR_I D
AND A.TABLE_X_CIF_I D=B.TABLE_X_CIF _ID)
FETCH FIRST 1000 ROWS ONLY) AS D
IF SQLCODE = 100 THEN LEAVE LOOP_TABLE_X_DE LTA; END IF;
COMMIT;
END LOOP;
COMMIT;

Of course you can play with the value in FETCH FIRST.

Cheers
Serge