The default sequence generator of Hibernate (SequenceHiloGenerator with allocationSize=50 ) generates a sequence value which is different from the corresponding oracle sequence's nextVal.


We have used allocationSize=50 for performance improvement but this creates problem by generating values which are not matching with the corresponding sequence values. In my case if nextVal of the sequence is 250 then the value generated by the JPA is far more than 250. I want the generator behaviour to return database sequence values while giving me performance enhancement by calling database sequence only once for every 50 inserts.


I know setting allocationSize=1 would generate value same as database sequence nextVal but in this case it makes select query for each insert to the table. I want to get rid of the selects for the inserts and thats why I moved on approach for HiliGenerator using allocationSize=50 but in this case facing problem of generated value not having in sync with the database sequence.


I would appreciate if anyone suggest a solution for this problem.


Regards

Yes, because on a Hilo-algorythm the formula usually is something like:

next_used_value = nextVal * allocationSize = 250 * 50



I you indeed want to have the generator behaviour exactly like a sequence,
then I suggest to try Sequence generator instead to SequenceHiloGenerator

Thanks for the suggestion @pb00067. But If I use Sequence generator instead of SequenceHiLogenerator then It would make database call for getting sequence value for every insert and will not give good performance. My goal is to improve performance of huge amount of inserts by minimizing database sequece select calls. And also I want the database sequence in sync with the values generated for the primary key id. Please suggest solution.

Hi aslamkhatri,

on one side you don't want database roundtrips for each generated primary key id,
on the other side you excpect the database sequence always being in sync with those.
How should something like this be possible without communication (roundtrips)?
Have you ever thaugth about?