deleting records in smaller chunks and issuing commit?

[boga ramakanth]

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]

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]

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:

SQL> create table foo2new as (select rownum n from all_tables);
 
Table created.
 
SQL> select count(*) from foo2new;
 
  COUNT(*)
----------
      9481
 
SQL> delete from foo2new where rownum < 1001;
 
1000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from foo2new;
 
  COUNT(*)
----------
      8481
 
SQL>

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]

My bad, I forgot truncate is a table command, not a row command.