30 January, 2007

Sequences : Should they be Gap Free ?

Frequently through your career as a DBA you are either asked "Why is there a gap in my Number series ?" OR are _told_ "the business-requirement/user/auditor CANNOT accept missing numbers". Then you have to keep your cool as you try to explain how Sequences work in Oracle. Sequences are designed to generate Unique Numbers, by default they do _NOT_ guarantee consecutive (ie "Gap Free") numbers. Some programmers try to beat sequences by using their own number generators, based on a single row in a table or the max() of a particular column in a table and causing either contention or repeated physical/FTS reads.
I was just reading Tom Kyte's responses at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986 -- "There is a fact about sequences -- an UNDENIABLE, UNESCAPABLE fact -- they are not gap free, will never be gap free, they will have gaps!".

No comments: