• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

deleting records in smaller chunks and issuing commit?

 
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
 
author
Posts: 4223
33
jQuery Eclipse IDE Java
  • 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.
 
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
Posts: 4223
33
jQuery Eclipse IDE Java
 
    Bookmark Topic Watch Topic
  • New Topic