oracle 休眠oracle序列产生大间隙
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5346147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
hibernate oracle sequence produces large gap
提问by sse
I am using hibernate 3 , oracle 10g. I have a table: subject. The definition is here
我正在使用 hibernate 3，oracle 10g。我有一张桌子：主题。定义在这里
CREATE TABLE SUBJECT
(
SUBJECT_ID NUMBER (10),
FNAME VARCHAR2(30) not null,
LNAME VARCHAR2(30) not null,
EMAILADR VARCHAR2 (40),
BIRTHDT DATE not null,
constraint pk_sub primary key(subject_id) USING INDEX TABLESPACE data_index
)
;
when insert a new subject, sub_seq is used to create an subject id, the definition is here
插入新主题时，sub_seq用于创建主题ID，定义在这里
create sequence sub_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 100
NOCYCLE ;
the Subject class is like this:
Subject 类是这样的：
@Entity
@Table(name="ktbs.syn_subject")
public class Subject {
@Id
@Column(name="subject_id")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SUB_SEQ")
@SequenceGenerator(name="SUB_SEQ", sequenceName = "SUB_SEQ")
private long subjectId;
private String fname;
private String lname;
private String emailadr;
private Date birthdt;
}
in the subject table , there have been 4555 subjects in the database loaded by plsql scripts from excel and the sub_sequence worked fine. subject ids range from 1--4555.
在主题表中，数据库中有 4555 个主题由来自 excel 的 plsql 脚本加载，并且 sub_sequence 工作正常。主题 ID 的范围为 1--4555。
however, when i added a subject from my application using hibernate, the sequence number jumped to 255050. After several days running, the subject ids generated by hibernate look like this
但是，当我使用 hibernate 从我的应用程序中添加一个主题时，序列号跳到了 255050。运行几天后，hibernate 生成的主题 ID 如下所示
270079
270078
270077
270076
270075
270074
270073
270072
270071
270070
270069
270068
270067
270066
270065
270064
270063
270062
270061
270060
270059
270058
270057
270056
270055
270054
270053
270052
270051
270050
265057
265056
265055
265054
265053
265052
265051
265050
260059
260058
260057
260056
260055
260054
260053
260052
260051
260050
255067
255066
255065
255064
255063
255062
255061
255060
255059
255058
255057
255056
255055
255054
255053
255052
255051
255050
4555
4554
4553
.
.
.
.
1
There are several large gaps: 4555 to 255051, 255067 to 260051, 265057 to 270051
有几个大的差距：4555到255051、255067到260051、265057到270051
this is a waste and not a desired behavior.
这是一种浪费，而不是一种理想的行为。
does anyone know why this happens and hot to fix it
有谁知道为什么会发生这种情况并且很容易修复它
Thanks
谢谢
回答by JB Nizet
I think that the problem comes from the fact that the sequence generator is not really a sequence generator, but a sequence hilo generator, with a default allocation size of 50. as indicated by the documentation : http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#entity-mapping-identifier
我认为问题来自这样一个事实，即序列生成器并不是真正的序列生成器，而是一个序列 hilo 生成器，默认分配大小为 50。如文档所示：http://docs.jboss.org/休眠/稳定/注释/参考/en/html_single/#entity-mapping-identifier
This means that if the sequence value is 5000, the next generated value will be 5000 * 50 = 250000. Add the cache value of the sequence to the equation, and it might explain your huge initial gap.
这意味着如果序列值为 5000，则下一个生成的值将是 5000 * 50 = 250000。将序列的缓存值添加到等式中，它可能解释了您巨大的初始差距。
Check the value of the sequence. It should be less than the last generated identifier. Be careful not to reinitialize the sequence to this last generated value + 1, because the generated valus would grow exponentially (we've had this problem, and had negative integer ids due to overflow)
检查序列的值。它应该小于最后生成的标识符。注意不要将序列重新初始化为最后生成的值 + 1，因为生成的值会呈指数增长（我们遇到过这个问题，并且由于溢出而具有负整数 id）
回答by sse
Agree with JB. But still thanks to PaulJ.
同意JB。但还是要感谢 PaulJ。
To be more specific to my annotation code below:
更具体地说明我的注释代码如下：
@Entity
@Table(name="ktbs.syn_subject")
public class Subject {
@Id
@Column(name="subject_id")
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SUB_SEQ")
@javax.persistence.SequenceGenerator(name="SUB_SEQ", sequenceName = "SUB_SEQ")
private long subjectId;
private String fname;
private String lname;
private String emailadr;
private Date birthdt;
}
If you use javax.persistence.SequenceGenerator, hibernate use hilo and will possibly create large gaps in the sequence. There is a post addressing this problem:
https://forum.hibernate.org/viewtopic.php?t=973682
如果您使用javax.persistence.SequenceGenerator，hibernate 使用 hilo 并且可能会在序列中产生很大的间隙。有一篇文章解决了这个问题：https:
//forum.hibernate.org/viewtopic.php?t=973682
There are two ways to fix this problem
有两种方法可以解决这个问题
- In the SequenceGenerator annotation, add
allocationSize = 1, initialValue= 1 instead of using javax.persistence.SequenceGenerator, use org.hibernate.annotations, like this:
@javax.persistence.SequenceGenerator( name = "Question_id_sequence", sequenceName = "S_QUESTION" ) @org.hibernate.annotations.GenericGenerator( name="Question_id_sequence", strategy = "sequence", parameters = { @Parameter(name="sequence", value="S_QUESTION") } )
- 在 SequenceGenerator 注释中，添加
allocationSize = 1, initialValue= 1 而不是使用 javax.persistence.SequenceGenerator，使用 org.hibernate.annotations，像这样：
@javax.persistence.SequenceGenerator( name = "Question_id_sequence", sequenceName = "S_QUESTION" ) @org.hibernate.annotations.GenericGenerator( name="Question_id_sequence", strategy = "sequence", parameters = { @Parameter(name="sequence", value="S_QUESTION") } )
I have tested both ways, which works just fine.
我已经测试了两种方式，效果很好。
回答by LeOn - Han Li
Actually having allocationSize=1 is fine if your sequence INCREMENT VALUEis 1 and you do not have the need of persisting a lot of entities.
However if you want to persist thousands or millions of records, the above setting could become a performance bottlenecksince every save need to fetch a id hence need a db read.
实际上，如果您的序列INCREMENT VALUE是 1 并且您不需要持久化很多实体，那么使用 allocationSize=1 就可以了。但是，如果您想保留数千或数百万条记录，上述设置可能会成为性能瓶颈，因为每次保存都需要获取 id，因此需要读取数据库。
To Solve this problem, we need to set the allocationSizeto something like 500 and sequence INCREMENT VALUEin DB also to 500, then most important add a hibernate setting hibernate.id.new_generator_mappingsto ask it to use the new sequence generator implementation, here i assume you set your hibernate properties in a java Config class:
要解决这个问题，我们需要将DB 中的allocationSize500 和序列设置INCREMENT VALUE为 500，然后最重要的是添加一个休眠设置hibernate.id.new_generator_mappings以要求它使用新的序列生成器实现，在这里我假设您在一个java配置类：
properties.setProperty("hibernate.id.new_generator_mappings", Boolean.toString(true));
This way, Hibernate will use SequenceStyleGeneratorrather than the old SequenceHiLoGeneratorto generate the ids. The SequenceStyleGeneratoris more jpa and oracle friendly. It generates identifier values based on an sequence-style database structure. Variations range from actually using a sequence to using a table to mimic a sequence.
这样，Hibernate 将使用SequenceStyleGenerator而不是旧的SequenceHiLoGenerator来生成 id。对SequenceStyleGeneratorjpa 和 oracle 更友好。它根据序列样式的数据库结构生成标识符值。变化范围从实际使用序列到使用表格来模拟序列。
Look at my post for more detail if you are in the same boat:
如果您在同一条船上，请查看我的帖子以获取更多详细信息：
vcfvct.wordpress.com/2016/04/23/jpa-sequencegenerator-with-allocationsize-1-performance-tuning/
vcfvct.wordpress.com/2016/04/23/jpa-sequencegenerator-with-allocationsize-1-performance-tuning/
回答by Jaydip Halake
Another solution is:
另一种解决方案是：
Use 'GenerationType.AUTO' instead of 'GenerationType.SEQUENCE' as strategy for @GeneratedValue, as below;
使用 'GenerationType.AUTO' 而不是 'GenerationType.SEQUENCE' 作为 的策略@GeneratedValue，如下所示；
@Id
@SequenceGenerator(name = "studentId", sequenceName = "student_Id")
@GeneratedValue(strategy = GenerationType.AUTO, generator="studentId")
private int studentId;
回答by PaulJ
If you read the following link, you will see that the problem is caused by the CACHE setting on your sequence creation command. Removing the cache setting will solve the problem to a degree - but does not take into account the possiblity of rollbacks, etc.
如果您阅读以下链接，您将看到问题是由序列创建命令中的 CACHE 设置引起的。删除缓存设置将在一定程度上解决问题 - 但没有考虑回滚等的可能性。
Link is: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:369390500346406705
链接是：http: //asktom.oracle.com/pls/apex/f?p=100:11:0 ::::P11_QUESTION_ID: 369390500346406705
The only way to resync your sequences now is to re-create the sequence, rename the current table and create the table again and then re-insert the records from the old table into the new table.
现在重新同步序列的唯一方法是重新创建序列，重命名当前表并再次创建表，然后将旧表中的记录重新插入新表中。
NOTE: The cache value for sequences is useful for large loads where 'x' sequence values are allocated at once. If you are using a transaction system where you do one insert at a time - then caching is not useful (or I should say - I've never found it useful).
注意：序列的缓存值对于一次性分配“x”序列值的大负载很有用。如果您使用的是一次插入一次的事务系统 - 那么缓存就没有用（或者我应该说 - 我从来没有发现它有用）。
NOTE: This is my understanding of the cache option for sequences. You can look up the Oracle Documentation on CREATE SEQUENCE commands for more info. But the link above should provide a reasonable answer to your question.
注意：这是我对序列缓存选项的理解。您可以查看有关 CREATE SEQUENCE 命令的 Oracle 文档以获取更多信息。但是上面的链接应该可以为您的问题提供合理的答案。
Thanks. Paul
谢谢。保罗
回答by Shekhar Khairnar
One solution to this we can configure sequence generator with allocationSize as:
对此的一种解决方案是，我们可以将带有 allocationSize 的序列生成器配置为：
@SequenceGenerator(name = "gen_name", sequenceName = "seq_name", allocationSize= 1)
回答by Olcay Tarazan
I had similar issues. sequence generator and sequence hilo generator are quite similar but have differences. In hibernate 3, hilo generator multiplies with default value 50. Therefore no need to increment DB sequence. On the other hand, later versions of hibernate uses sequence generator by default. Therefore DB increment by 50 is required.
我有类似的问题。序列生成器和序列hilo生成器非常相似但有区别。在 hibernate 3 中，hilo 生成器乘以默认值 50。因此不需要增加 DB 序列。另一方面，更高版本的 hibernate 默认使用序列生成器。因此需要将 DB 增量为 50。
I had this issue which have multiple hibernate versions (3 and 5). Same configuration worked fine (incremented by 1 in DB). But failed in hibernate 5. Therefore I update my persistence.xml as below. This ensures hilo generation
我有这个问题，它有多个休眠版本（3 和 5）。相同的配置工作正常（在 DB 中增加 1）。但是在 hibernate 5 中失败了。因此我更新了我的persistence.xml 如下。这确保了 hilo 生成
<property name="hibernate.id.new_generator_mappings" value="false" />
回答by Bhabani Sankar Sahoo
The most successfull answer would be:
最成功的答案是：
@Id
@SequenceGenerator (name = "id_sequence", sequenceName = "sq50")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "id_sequence")
public int getId() {
return id;
}
