Delete and commit

[Former Member]

Hi,

I want to delete huge number of records from a database table. I am using the statement as

DELETE FROM XXX

WHERE ABC = S_ABC.

Above statement will try commit all the records at one time. since data is huge, can you suggest some good/optimized way of doing this.

Thanks,

apps

[Former Member]

Hi Apps,

There is nothing wrong with the approach you use.

If S_ABC is a range or a aselect option, then you may want to correct your statement like below.

DELETE FROM XXX

WHERE ABC in S_ABC. "Deletes all the records that match S_ABC

Regards,

Ravi Kanth

[Former Member]

thanks for your reply.

But my basis consultant says that program tries to commit huge number of records at one time so there is a problem.

Apps

[gopi_narendra]

This really should not be a problem.

The code what you wrote is correct and is good wrt performance.

Regards

Gopi

[Former Member]

Your basis consultant is correct. The problem will be that if you delete too many records, the rollback segment may run out of space and the program dump.

You can get around this by deleting data in chunks. Change your statement to:

DELETE FROM XXX
  WHERE ABC = P_ABC
    AND CDE = some value.

Do this for all values of CDE.

Rob

[Former Member]

Thanks for your reply.

Can you tell me what should be ideal counter for commit.

I mean how many records should i bundle (e.g 50,000).

Thanks,

Apps

[Former Member]

That's a really good question and not one that I can answer. The amount of space used up in the rollback segment will be determined by the number of records being deleted and the size of each record. The size of the rollback segment will also determine how many records can be deleted. Also you may have different sized segments for over night processing.

A final wrinkle would be that the sizes may vary between the test systems and the production system.

So - you need to experiment. I would start with a size of 10,000, but 50,000 might be OK too.

Rob