oracle 征求意见：所有表的一个序列

Asking for opinions : One sequence for all tables

提问by Rob van Laarhoven

Here's another one I've been thinking about lately. We have concluded in earlier discussions : 'natural primary keys are bad, artificial primary keys are good.' Working with Hibernate earlier I have seen that Hibernate default creates one sequence for all tables. At first I was puzzled by this, why would you do this. But later I saw the advantage that it makes linking parents and children fool proof. Because no tables have the same primary key value, accidentally linking a parent with a table that is not a child gives no results.

这是我最近一直在考虑的另一个问题。我们在前面的讨论中得出结论：“自然主键不好，人工主键好”。之前使用 Hibernate 我已经看到 Hibernate 默认为所有表创建一个序列。一开始我很困惑，你为什么要这样做。但后来我看到了它的优势，它可以让父母和孩子的链接变得万无一失。因为没有表具有相同的主键值，所以意外地将父表与不是子表的表相关联不会产生任何结果。

Does anyone see any downsides to this approach. I only see one : you cannot have more than 999999999999999999999999999 records in your database.

有没有人看到这种方法的任何缺点。我只看到一个：您的数据库中的记录不能超过 999999999999999999999999999。

采纳答案by Aaron Digulla

Depending on how sequences are implemented in the database, always hitting the same sequence can be better or worse. When only a few or only one thread request new values, there will be no locking issues. But a bad implementation could cause congestion.

根据序列在数据库中的实现方式，总是命中相同的序列可能更好也可能更糟。当只有少数或只有一个线程请求新值时，不会出现锁定问题。但是一个糟糕的实现可能会导致拥塞。

Another problem is rolling back transactions: Sequences don't get rolled back (because someone else might have requested a higher value already), so you can have large gaps which will eat your number space much more quickly than you might expect. OTOH, it will take some time to eat 2 or 4 billion IDs (if you "only" use 32 bit (signed) ints), so it's rarely an issue in practice.

另一个问题是回滚事务：序列不会回滚（因为其他人可能已经请求了更高的值），因此您可能会有很大的间隙，这会比您预期的更快地消耗您的数字空间。OTOH，吃掉 2 或 40 亿个 ID 需要一些时间（如果你“只”使用 32 位（有符号）整数），所以这在实践中很少成为问题。

Lastly, you can't easily reset the sequence if you have to. But if you need to have a restarting sequence (say, number of records since midnight), you can tell Hibernate to create/use a second sequence.

最后，如果必须，您不能轻易重置序列。但是，如果您需要重新启动序列（例如，自午夜以来的记录数），您可以告诉 Hibernate 创建/使用第二个序列。

A major advantage is that you can uniquely identify objects anywhere in the DB just by the ID. That means you can severely cut down the log information you write in the production system and still find something if you only have the ID.

一个主要优点是您可以仅通过 ID 唯一标识数据库中任何位置的对象。这意味着你可以大幅减少你在生产系统中写入的日志信息，如果你只有 ID，仍然可以找到一些东西。

回答by Tony Andrews

There could be performance issues with all code getting values from a single sequence - see this Ask Tom thread.

从单个序列中获取值的所有代码都可能存在性能问题 - 请参阅此 Ask Tom 线程。

回答by Erich Kitzmueller

I prefer having one sequence per table. This comes from one general observation: Some tables ("master tables") have a relatively small row count and have to be kept "forever". For example, the customer table in an ERP.

我更喜欢每张桌子有一个序列。这来自一个普遍的观察：一些表（“主表”）的行数相对较少，必须“永远”保留。例如，ERP 中的客户表。

In other tables ("transaction tables"), many rows are generated perpetually, but after some time, those rows can be archived (or simply deleted). The most extreme example is a tracing table used for debugging purposes; it might grow by hundreds of rows per second, but each row is obsolete after a few days.

在其他表（“事务表”）中，会永久生成许多行，但一段时间后，这些行可以存档（或简单地删除）。最极端的例子是用于调试目的的跟踪表；它可能每秒增长数百行，但几天后每一行都会过时。

Small IDs in the master tables make it easier when working directly on the database, e.g. for debugging purposes.

当直接在数据库上工作时，主表中的小 ID 更容易，例如用于调试目的。

select * from orders where customerid=415

vs

对比

select * from orders where customerid=89461836571

But this is only a minor issue. The bigger issue is cycling. If you use one sequence for all tables, you simply cannot let it restart. With one sequence per table, you can restart the sequences for the transaction tables when you have archived or deleted the old data. Master tables hardly ever have that problem, since they grow much slower.

但这只是一个小问题。更大的问题是骑自行车。如果对所有表都使用一个序列，则根本无法让它重新启动。对于每个表一个序列，您可以在归档或删除旧数据后重新启动事务表的序列。主表几乎没有这个问题，因为它们的增长速度要慢得多。

I see little value in having only one sequence for all tables. The arguments told so far do not convince me.

我认为所有表只有一个序列没有什么价值。到目前为止的论据都不能说服我。

回答by WW.

There are a couple of disadvantages of using a single sequence:-

使用单个序列有几个缺点：-

• reduced concurrency. Handing out the next sequence value involves synchronisation. In practice, I do not think this is likely to be a big problem
• Oracle has special code when maintaining btree indexes to detect monotonically increasing values and balance the tree approriately
• The CBO might have a better time estimating range queries on the index (if you ever did this) if most values were filled in

• 减少并发。分发下一个序列值涉及同步。在实践中，我不认为这可能是一个大问题
• Oracle 在维护 btree 索引时有特殊的代码来检测单调递增的值并适当地平衡树
• 如果填充了大多数值，CBO 可能有更好的时间来估计索引的范围查询（如果您曾经这样做过）

An advantage might be that you can determine the order of inserts amongst different tables.

一个优点可能是您可以确定不同表之间的插入顺序。

回答by mikesalera

Certainly there are pros and cons to the one-sequence versus one-sequence-per-table approach. Personally I find the ability to assign a truly unique identifier to a row, making each id column a uuid, to be enough of a benefit to outweigh any disadvantages. As Aaron D. succinctly writes:

当然，一个序列与每表一个序列的方法各有利弊。就我个人而言，我发现能够为一行分配一个真正唯一的标识符，使每个 id 列成为一个 uuid，足以抵消任何缺点。正如 Aaron D. 简洁地写道：

you can uniquely identify objects anywhere in the DB just by the ID

您可以仅通过 ID 唯一标识数据库中任何位置的对象

And, for most applications, due to the way Hibernate3 batches IMPORT statements, this will not be a performance bottleneck unless massive amounts of records are vying for the same db resource (SELECT hibernate_sequence.nextval FROM dual).

而且，对于大多数应用程序，由于 Hibernate3 批处理 IMPORT 语句的方式，这不会成为性能瓶颈，除非大量记录争夺相同的数据库资源（SELECT hibernate_sequence.nextval FROM dual）。

Also, this sequence mapping is not supported in the latest release (1.2) of Grails. Though it was supported in Grails 1.1 (!). It now requires subclassing one of the Hibernate dialect classes as a workaround.

此外，Grails 的最新版本 (1.2) 不支持此序列映射。尽管 Grails 1.1 (!) 支持它。它现在需要子类化 Hibernate 方言类之一作为解决方法。

For those using Grails/GORM, have a look at this JIRA entry:

对于那些使用 Grails/GORM 的人，看看这个 JIRA 条目：

Oracle Sequence mappings ignored

忽略 Oracle 序列映射