13

I am using Oracle

What is difference when we create ID using max(id)+1 and using sequance.nexval,where to use and when?

Like:

insert into student (id,name) values (select max(id)+1 from student, 'abc');

and 

insert into student (id,name) values (SQ_STUDENT.nextval, 'abc');

SQ_STUDENT.nextval sometime gives error that duplicate record...

please help me on this doubt

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
Priya Prajapati
  • 304
  • 1
  • 4
  • 10

4 Answers4

35

With the select max(id) + 1 approach, two sessions inserting simultaneously will see the same current max ID from the table, and both insert the same new ID value. The only way to use this safely is to lock the table before starting the transaction, which is painful and serialises the transactions. (And as Stijn points out, values can be reused if the highest record is deleted). Basically, never use this approach. (There may very occasionally be a compelling reason to do so, but I'm not sure I've ever seen one).

The sequence guarantees that the two sessions will get different values, and no serialisation is needed. It will perform better and be safer, easier to code and easier to maintain.

The only way you can get duplicate errors using the sequence is if records already exist in the table with IDs above the sequence value, or if something is still inserting records without using the sequence. So if you had an existing table with manually entered IDs, say 1 to 10, and you created a sequence with a default start-with value of 1, the first insert using the sequence would try to insert an ID of 1 - which already exists. After trying that 10 times the sequence would give you 11, which would work. If you then used the max-ID approach to do the next insert that would use 12, but the sequence would still be on 11 and would also give you 12 next time you called nextval.

The sequence and table are not related. The sequence is not automatically updated if a manually-generated ID value is inserted into the table, so the two approaches don't mix. (Among other things, the same sequence can be used to generate IDs for multiple tables, as mentioned in the docs).

If you're changing from a manual approach to a sequence approach, you need to make sure the sequence is created with a start-with value that is higher than all existing IDs in the table, and that everything that does an insert uses the sequence only in the future.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
10

Using a sequence works if you intend to have multiple users. Using a max does not.

If you do a max(id) + 1 and you allow multiple users, then multiple sessions that are both operating at the same time will regularly see the same max and, thus, will generate the same new key. Assuming you've configured your constraints correctly, that will generate an error that you'll have to handle. You'll handle it by retrying the INSERT which may fail again and again if other sessions block you before your session retries but that's a lot of extra code for every INSERT operation.

It will also serialize your code. If I insert a new row in my session and go off to lunch before I remember to commit (or my client application crashes before I can commit), every other user will be prevented from inserting a new row until I get back and commit or the DBA kills my session, forcing a reboot.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • but some time why I get duplicate record error with sequence.nextval and it works when I run same query with max(id)+1 – Priya Prajapati Jun 20 '13 at 10:16
  • 2
    @PriyaPrajapati - because your sequence's current value is lower than the maximum ID in the table. The sequence is not modified if you manually insert a value in the table. You should not mix the two approaches - get the sequence so it is higher than the table values, and then only use the sequence. – Alex Poole Jun 20 '13 at 10:18
4

To add to the other answers, a couple of issues.

Your max(id)+1 syntax will also fail if there are no rows in the table already, so use:

Coalesce(Max(id),0) + 1

There's nothing wrong with this technique if you only have a single process that inserts into the table, as might be the case with a data warehouse load, and if max(id) is fast (which it probably is).

It also avoids the need for code to synchronise values between tables and sequences if you are moving restoring data to a test system, for example.

You can extend this method to multirow insert by using:

Coalesce(max(id),0) + rownum

I expect that might serialise a parallel insert, though.

Some techniques don't work well with these methods. They rely of course on being able to issue the select statement, so SQL*Loader might be ruled out. However SQL*Loader has support for this technique in general through the SEQUENCE parameter of the column specification: http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#i1008234

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

Assuming MAX(ID) is actually fast enough, wouldn't it be possible to:

  • First get MAX(ID)+1
  • Then get NEXTVAL
  • Compare those two and increase sequence in case NEXTVAL is smaller then MAX(ID)+1
  • Use NEXTVAL in INSERT statement

In that case I would have a fully stable procedure and manual inserts would also be allowed without worrying about updating the sequence

Stefanos Kargas
  • 10,547
  • 22
  • 76
  • 101
  • You're introducing gaps, which isn't really a problem; and you've still got a window where two sessions query the same `max(id)` value, though the `nextval` calls should make it work. You can't do this in plain SQL though, and allowing a mix is still messy. Another similar approach is to attempt the insert with `nextval` in a loop, exiting if you *don't* get an ORA-00001, assuming there's a PK/UK on the column. That avoids having to look for the max, and fills in gaps, which may or may not be desirable. Only ever using the sequence is easier though... – Alex Poole Jul 31 '13 at 11:02