Oracle：max(id)+1 和 sequence.nextval 之间的区别

Oracle: difference between max(id)+1 and sequence.nextval

提问by Priya Prajapati

I am using Oracle

我正在使用甲骨文

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

当我们创建IDusingmax(id)+1和 using 时sequance.nexval，在哪里使用和何时使用有什么区别？

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.nextvalsometime gives error that duplicate record...

SQ_STUDENT.nextval有时会出现重复记录的错误...

please help me on this doubt

请帮助我解决这个疑问

回答by Alex Poole

With the select max(id) + 1approach, 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).

使用该select max(id) + 1方法，同时插入的两个会话将从表中看到相同的当前最大 ID，并且都插入相同的新 ID 值。安全使用它的唯一方法是在开始事务之前锁定表，这很痛苦并且会序列化事务。（正如 Stijn 指出的，如果删除最高记录，则可以重用值）。基本上，永远不要使用这种方法。（偶尔可能有一个令人信服的理由这样做，但我不确定我是否见过）。

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.

使用序列获得重复错误的唯一方法是表中已存在 ID 高于序列值的记录，或者某些内容仍在插入记录而不使用序列。因此，如果您有一个手动输入 ID 的现有表，比如 1 到 10，并且您创建了一个默认起始值​​为 1 的序列，则使用该序列的第一个插入将尝试插入一个 ID 为 1 - 它已经存在. 在尝试 10 次之后，序列会给你 11，这会起作用。如果您随后使用 max-ID 方法执行下一个将使用 12 的插入，但序列仍将在 11 上，并且在您下次调用时也会为您提供 12 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).

序列和表没有关系。如果将手动生成的 ID 值插入到表中，序列不会自动更新，因此这两种方法不会混合。（除其他外，如文档中所述，相同的序列可用于为多个表生成 ID）。

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.

如果您要从手动方法更改为序列方法，则需要确保创建序列的起始值高于表中所有现有 ID，并且执行插入的所有操作都使用该序列只有在未来。

回答by Justin Cave

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

如果您打算拥有多个用户，则使用序列有效。使用 amax不会。

If you do a max(id) + 1and you allow multiple users, then multiple sessions that are both operating at the same time will regularly see the same maxand, 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 INSERTwhich may fail again and again if other sessions block you before your session retries but that's a lot of extra code for every INSERToperation.

如果您执行 amax(id) + 1并允许多个用户，那么同时运行的多个会话将定期看到相同的内容max，因此将生成相同的新密钥。假设您已正确配置约束，这将产生您必须处理的错误。您将通过重试来处理它，INSERT如果其他会话在您的会话重试之前阻止您，这可能会一次又一次地失败，但这对于每个INSERT操作来说都是很多额外的代码。

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.

它还将序列化您的代码。如果我在会话中插入一个新行并在我记得提交之前去吃午饭（或者我的客户端应用程序在我可以提交之前崩溃），则将阻止其他所有用户插入新行，直到我返回并提交或DBA 终止了我的会话，强制重新启动。

回答by David Aldridge

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:

如果表中已经没有行，您的 max(id)+1 语法也会失败，因此请使用：

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).

如果您只有一个进程插入到表中（数据仓库负载可能就是这种情况），并且 max(id) 速度很快（可能就是这种情况），那么这种技术没有任何问题。

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

某些技术不适用于这些方法。它们当然依赖于能够发出 select 语句，因此 SQL*Loader 可能被排除在外。但是，SQL*Loader 通常通过列规范的 SEQUENCE 参数支持此技术：http: //docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#i1008234

回答by Stefanos Kargas

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

假设 MAX(ID) 实际上足够快，难道不可能：

• 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

• 先得到MAX(ID)+1
• 然后得到 NEXTVAL
• 比较这两个并增加序列，以防 NEXTVAL 小于 MAX(ID)+1
• 在 INSERT 语句中使用 NEXTVAL

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

在那种情况下，我将有一个完全稳定的程序，并且还允许手动插入，而不必担心更新序列