hibernate oracle sequence produces large gap

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

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.

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)

Agree with JB. But still thanks to 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

There are two ways to fix this problem

1. In the SequenceGenerator annotation, add allocationSize = 1, initialValue= 1

2. 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")     })

I have tested both ways, which works just fine.

Actually having allocationSize=1 is fine if your sequence INCREMENT VALUE is 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 bottleneck since every save need to fetch a id hence need a db read.

To Solve this problem, we need to set the allocationSize to something like 500 and sequence INCREMENT VALUE in DB also to 500, then most important add a hibernate setting hibernate.id.new_generator_mappings to ask it to use the new sequence generator implementation, here i assume you set your hibernate properties in a java Config class:

properties.setProperty("hibernate.id.new_generator_mappings", Boolean.toString(true));

This way, Hibernate will use SequenceStyleGenerator rather than the old SequenceHiLoGenerator to generate the ids. The SequenceStyleGenerator is 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.

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/