Against my will, I am told to use the following method to generate a new pk value.

    VALUES((SELECT MAX(SM_SEQ)+1 FROM SERVER_MONITERING), #cpuStat.combinedUsed#, #memStat.total#, #memStat.used#, #diskStat.size#, #diskStat.used#, #serverIP#, 'KW', 'N', TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'))

I told him it still looks like there could be duplicate SM_SEQ values.

However, I don't know for sure whether this approach will eliminate duplication or not.

  1. Will the approach above eliminate the chance of duplicate values ?
  2. Is this a good practice ?
  3. Will a sequence.nextval eliminate the chance of duplicate values ?
  4. What is the best practice ?


Sorry, I forgot to mention.
There are multiple devs and their own code that inserts into this table and they all use select max()+1. I am a "guest" dev at this company. So I have little influence on their decision. If I switch to using a sequence only by myself, other devs will still select max()+1 and there will be collision between the two approaches.
Probably, I would have to tell all of them why even nesting a select max()+1 won't solve their problem, if it truly would not.

  • is there a reason you can't use the actual sequence to generate the SM_SEQ value? Dec 7, 2015 at 2:11
  • 1
    I was told by my superior to use the approach above. I would have to tell him why a sequence is better than his approach. So I asked this question and specifically, question #3. Also, technically, I'm not allowed to modify their database. I only asserted my feeling of impending doom but I don't have the knowledge to prove.
    – Ascendant
    Dec 7, 2015 at 2:21

1 Answer 1


Assuming that you allow multiple users in your application, max()+1 will guarantee that you will get duplicate values. Therefore, it is a bad practice. It's also less efficient but that is a lesser concern. If you have two sessions, session 1 gets the max() + 1, and session 2 tries to get the max() + 1 before session 1 commits (or rolls back), session 2 will get the same value that was given to session 1.

Using a sequence will eliminate the possibility of duplicates. Use a sequence.

  • Using a sequence will be indeed my preference. However, in my specific case, that could cause some issue with the code along with bothering all the other devs to change their code. Do you have any knowledge regarding my question #1 ?
    – Ascendant
    Dec 7, 2015 at 4:48
  • @Ascendant I answered #1 already. Max()+1 guarantees duplicates in multiuser systems. Dec 7, 2015 at 4:50
  • Sorry to bother you but I wanted to make it extra clear. So nesting a select max() within the insert statement does not eliminate the chance of duplicate values ? I ask you this because my superior specifically told me to do that because he said this(nesting select max() within insert) will eliminate duplicate values or give less chance of multiple sessions mixed over because of little time between select max() and insert.
    – Ascendant
    Dec 7, 2015 at 5:07
  • @Ascendant - Nesting has nothing to do with it. As I said, max()+1 does not guarantee uniqueness. If you have multiple users, you will get duplicates. Do the experiment that I outlined in my answer-- open two session, do an insert in session 1, don't commit, go to session 2, do the same insert. If you're using max()+1, session 2 will get the same value that session 1 got. Dec 7, 2015 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.