Search This Blog

Monday, December 3, 2012

Service Request Numbers Are Not in Sequential Order

The Service Request automatic number generation is controlled by database sequence 'CS_INCIDENTS_NUMBER_S'.
That sequence comes seeded with an INCREMENT_BY value of 2 and a CACHE_SIZE value of 100.

That can be verified with the following query:

select sequence_name, increment_by, cache_size
from dba_sequences
where sequence_name in (
'CS_INCIDENTS_S',
'CS_INCIDENTS_NUMBER_S');

That will show the following:


SEQUENCE_NAME                  INCREMENT_BY CACHE_SIZE
------------------------------ ------------ ----------
CS_INCIDENTS_NUMBER_S               2           100
CS_INCIDENTS_S                      2           100

Due the reason that sequence numbers are cached, if for any reason the cache is flushed (like when the database is bounced) the cached numbers are lost. But the sequence itself is already advanced so, when the sequence numbers are cached again they will continue from the last number in the sequence and you will see a gap.
The gap will change depending on how many cached numbers were "lost" hence, that gap could be different and will look like the service request numbers are generated in random increments.

If you want to have a sequential order for Service Request numbers you can modify the sequence to have an INCREMENT_BY of 1 and no cache. That can be done with the following command:

alter sequence CS.CS_INCIDENTS_NUMBER_S INCREMENT BY 1 NOCACHE;

CS_INCIDENTS_NUMBER_S sequence will control the Service Request number.
If you want the INCIDENT_ID number in CS_INCIDENTS_ALL_B table to be also sequential then you need to modify CS_INCIDENTS_S. You can use the following command:

alter sequence CS.CS_INCIDENTS_S INCREMENT BY 1 NOCACHE;

1 comment:

Joaquin Gonzalez said...

Using a sequence with no cache and increment 1 does not guarantee free gap inserts. Think what happens when you rollback an insert statement.