Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016