Originally posted by Atul Mishra:
For the time being I am planning to do a batch update here using three delete statements.
I assume there wont be any issues here. If anyone sees any please let me know.
Thanks
That should be fine. Assuming you're using PreparedStatments correctly, cascade delete might be just the tiniest bit faster on Oracle than using batch, but the difference isn't so much that I would worry about it at all.
By the way, I'm assuming that child column in your foreign key constraint is indexed; if not, your performance will stink as your tables get big, no matter how you do the deletes.
For example:
Table A - primary key on column named ID
Table B - foreign key constraint between B.PARENT_ID and A.ID
Every time a row in A is deleted (or has ID updated), the constraint on B is checked. If there's not an index on PARENT_ID, then the entire table B must be scanned which is relatively slow on big tables; if there is an index, the check is very quick.