Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

deleting records in smaller chunks and issuing commit?

 
boga ramakanth
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My bad, I forgot truncate is a table command, not a row command.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic