Sequence increment by 50 instead of 1

Question

I created an Oracle sequence:

Create sequence seq_acteurs start with 1 increment by 1;

Normally the sequence must be incremented by 1, but when insert into Acteurs table, the sequence s incremented by 50! That doesn't seem logical. Why is this so?

Any help will much apreciated.

Ackack

Are you asking what could cause a sequence to increment by 50 when normally it increments by 1? – Witherspoon

Why do you need a gap free sequence? – Beer

did my answer solve your issue somewhat? Thx – Richmond

Answer 1

Sequence numbers are not fetched one at a time when you need them. NOrmally, the DB caches a certain number of sequence numbers in memory. WHen the cached sequences are all used up, then the DB fetches a new block of sequence numbers into cache. Now if the DB crashes for some reason while the cache still has some unused numbers, then there could be some gaps in your sequence numbers.

Richmond

also if there is a rollback in a transaction using a seqno, sequence numbers disappear – Beer

i don't know but from what i saw it's always incremented by 50 when i add the first record the sequences gives it 50 as id, when i add the second the sequence gives it 100 then 150 then 200 and so on always incrementing by 50 – Ackack

Answer 2

Sequences does not guarantee that they generate a gap free sequence of numbers.

You can minimize the gaps by specifying nocache, but this does still not guarantee gap free sequences, and might create a resource contention.

Example from the documentation:

CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

Beer

There's nothing wrong with using nocache when the circumstances indicate it. Otherwise Oracle wouldn't have implemented it. – Sejant

ok but can you tell me how i can use "nochache"? thanks alot for your time – Ackack

sorry for the late and thanks for your time, i tryed your code but still the same problem... still incremeting by 50 this time the sequence started from 50000 then 50050 then 50100 etc – Ackack

Answer 3

In your entity class add this and will increase by 1

allocationSize=1

this is an example:

@SequenceGenerator(name = "ECP_EVALUACION_SEQ", sequenceName = "SCORTN.ECP_EVALUACION_SEQ",allocationSize=1)

Cervix