• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Generating ID's strategy

 
Ben Narendren
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a table which gets a lot of records everyday and a lot of these records are deleted once a week by a batch job. I am running out of of id's very fast and I am thinking of a different strategy to generate id's. One idea I have is to reuse the deleted id's somehow. Any ideas out there?
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could reset the internal ID starting point.

WP
 
Paul Clapham
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Or when you reach the upper limit on the value of ID you could roll over and start at 0 or 1 again.
 
Ben Narendren
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your input and I apologize for the late reply. The deletion process I mentioned in not on a continuous block of id's. So after the deletion process there are mostly holes in the sequence. So if I reset the id to the 0 or 1, that will cause duplicate id's to be generated. That's why I need a better strategy to deal with this.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You'd have to renumber the remaining items periodically to free up blocks of unused ids. Complicated job, in my opinion. I'd try to increase the width of the ID column instead. If you can manage using a 64 bit number (easily to be handled by Java's long/Long), you can allocate million IDs each second continuously for several hundred thousands of years. That should be more than sufficient.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic