This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Use sequence or select max(field) from table to get next id? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Use sequence or select max(field) from table to get next id?" Watch "Use sequence or select max(field) from table to get next id?" New topic
Author

Use sequence or select max(field) from table to get next id?

alex han
Ranch Hand

Joined: Dec 13, 2002
Posts: 46
when insert a record to oracle, use sequence or use "select max(id) from table" to get next id??which is efficiently?
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Well, the sequence table is probably safer, in that you are safe from dirty reads. In the Max query you might get a result, add one to it, and when you go to save, someone has already used that number and you get a Primary Key violation.
With a Sequence, you can have Oracle handle it all for you. just use NextVal and no other user will get that number. You can use it in the INSERT statement, or if you need the number first in a GUI, then call SELECT sequence.NextVal from Dual and get the next value.
Hope that helps.
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
alex han
Ranch Hand

Joined: Dec 13, 2002
Posts: 46
thanks ,i will use sequence
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Use sequence or select max(field) from table to get next id?
 
Similar Threads
Auto Increment ID Field Table in the Oracle Database
Hibernate - SQLGrammarException : could not get next sequence value
Determined identity key value
storing autogenerated value into variables in procedures
Sequence Problem