Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to delete huge amount of records

 
chen young
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a table that contain huge amount of records.
There is a problem to run delete sql on this capacity.
Is it possible to do commit after number of records ?

Thanks
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Which database are you using ?

Whenever you delete any record(s), your RDBMS keeps them in Rollback segment, so in case if you issue a rollback command old data can be restored. In case you delete huge amount of record, you need to make sure that size of those records should not exceed what rollback segment can take care of.

If you need to delete all records you can truncate the table. on the other side write a small PL/SQL procedure and delete the inside loop based on row number and commit.

Alternatively you can run the same query from you SQL console multiple times. For example If you are working on oracle



Shailesh
 
chen young
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am using informix

I need to delete all records from table via java class (not from the console)
How can I check that: "the size of those records should not exceed what rollback segment can take care of" ?
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
for rollback segment information you can check with your DBA , but rather going in such details I would advice you to write a java program , and delete aprroximately 50K or 100K records in one go.

1. Execute a query to get the count of satisfying record
2. Identify the number of loop iteration by dividing total record with number you decide to delete in one go
3. commit inside the loop.

by the way, how many records are there which you want to delete from table.

Shailesh
 
chen young
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
700000
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can also use PreparedStatement#addBatch() in a loop to add every record to a batch and execute it only once afterwards. Costs only one DB hit instead of a hit for every row which would be far more expensive and slow.
 
chen young
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you do not need to be able to rollback the changes, this can work from java (please test first - and - yes - it is not a best practice):
Drop table, then recreate it and it's constraints.

and...

I know it has to work from java, but I am still pointing you to this console info:
LOAD FROM /dev/null OF DEL REPLACE INTO department
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, you wanted to delete ALL rows.

What's wrong with "DELETE FROM table" without a WHERE?
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might exceed the rollback segment space.
The issue the OP has is that when lots of records are deleted, the undo log might become bigger as allowed.
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If rollback is not of interest, turn that off.
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Let's see what Angus Young (would that be THE Angus Young?) replies. You and I understood the question differently. It's up to Angus to clarify.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bauke Scholtz wrote:If rollback is not of interest, turn that off.

... or use a DDL statement instead of a DML one (since they never run ina transaction). e.g. :

 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nice suggestion, didn't knew that behaviour.

Just to add, truncate isn't part of SQL92 and thus not supported by all database(version)s. Although it is less or more adopted by many DB's and likely to be part of the upcoming SQL92 successor. Since a certain version Informix supports Truncate as well, so this should be the way to go.
 
chen young
Ranch Hand
Posts: 197
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all for your help

I cannot do drop table or truncate.
I think another why is to execute delete every N records instead all of them.

Anyway I will check it

Thanks
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic