When is a Sequence Number Not Sequential?
A sequence number or an identity column guarantees the following: Number served is unique. Number served is sequential (ascending order) - Identity columns guarantees this; sequence number in Oracle RAC mode doesn't, unless ORDER is used. However: It does not guarantee the sequence is gap-free Sequence number/ identity columns do not partake in transactions Number loss/ gaps happen due to the following: Served number does not get utilised (typically due to transaction rollback) Server restart/ failures - database servers tend to cache sequences in memory for performance reasons. E.g. Oracle Sequence number set-up to be CACHEd in memory. According to Oracle 11gR2 Database Documentation : for the Sequence database concept, use of Oracle Sequence does not guarantee gap-free set of numbers. If your application requires a gap-free set of numbers, then you cannot use Oracle sequences . You must serialize activities in the database using your own developed cod...
Comments