File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 Java 8 in Action this week in the Java 8 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
storing autogenerated value into variables in procedures
Determined identity key value
Sequence Problem
Hibernate - SQLGrammarException : could not get next sequence value
Auto Increment ID Field Table in the Oracle Database