File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes deleting records in smaller chunks and issuing commit? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "deleting records in smaller chunks and issuing commit?" Watch "deleting records in smaller chunks and issuing commit?" New topic
Author

deleting records in smaller chunks and issuing commit?

boga ramakanth
Greenhorn

Joined: Jul 10, 2006
Posts: 2
i have some queries which perform deletion process and can able to delete millions of records and commiting at the end. the database holds the information in its transaction logs while they are running and there's always possibility that the transaction logs will fill up.

So for instance a statement like:
delete from tradert.rt_trd_aud where src_sys_cd='ITS' and fmly_trd_id in
(select fmly_trd_id from TRADERT.RT_CLEANUP where src_sys_cd='ITS');

might cause 400,000 records to build up in the transaction log and everything gets committed at the end.

But in java, there's a posibility to have a list of 400,000 records in memory and go through 1000 records at a time, issuing a delete statement, then issuing a commint, till it reaches the 400,000 records. this frees up the transaction log.
This i need to do in Springs framework. Is there any way which i can do this in Springs Framework.


thanks
ramakanth
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

What you might want is a truncate statement instead of a delete. This issues an immediate delete with no rollback possibility. Since you are deleting 1000 at a time and committing, it sounds like you want to skip rollback all together so truncate is probably the best choice.


My Blog: Down Home Country Coding with Scott Selikoff
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Scott Selikoff:
What you might want is a truncate statement instead of a delete. This issues an immediate delete with no rollback possibility. Since you are deleting 1000 at a time and committing, it sounds like you want to skip rollback all together so truncate is probably the best choice.


TRUNCATE is a SQL statement that is not supported on all databases. For example, Oracle and MySQL have it, and Postgres (apparently) does not.

Furthermore, it is almost the equivalent of dropping and recreating the table. The OP only wants to delete rows that meet a certain criteria, not all rows.

In some databases (e.g. Oracle and Postgres), this problem is sometimes resolved by partitioning the table and then dropping the appropriate partition (which may or may not produce logging, depending on the DB). Other times, disableing logging and then reanabling and doing a full backup is the solution. Other times, deleting in smaller batches will work. The best way to do that depends on your database. In Oracle, you can limit a DELETE to some number of rows with a "WHERE rownum < [value]" clause.

For example:


Most frameworks and O/R mappers I know don't let you work this way, using DB-dependent features, but I don't know anything about Spring. In the ones I do know, you have to have a selection criteria that effictively limits the delete to the size you want.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

My bad, I forgot truncate is a table command, not a row command.
 
GeeCON Prague 2014
 
subject: deleting records in smaller chunks and issuing commit?