Best way to generate unique and consecutive numbers in Oracle

Question

I need to generate unique and consecutive numbers (for use on an invoice), in a fast and reliable way. I currently use an Oracle sequence, but in some cases, the generated numbers are not consecutive because of exceptions that may occur.

I thought a couple of solutions to manage this problem, but neither of them convincing me. What solution do you recommend?

1. Use a select max ()

    SELECT MAX (NVL (doc_num, 0)) +1 FROM invoices
   

2. Use a table to store the last number generated for the invoice.

    UPDATE docs_numbers
        SET last_invoice = last_invoice + 1
   

3. Another Solution?

Answer 1

• Option 1 can always be made to fail some way in an environment with concurrent users.
• Option 2 will work, but will limit scalability — obligatory Tom Kyte reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476

As he recommends, you should really review the necessity for the "no gaps" requirement.

Answer 2

The gaps appear if a transaction uses a sequence number but is then rolled back.

Maybe the answer is not to assign the invoice number until the invoice can't be rolled back. This minimizes (but probably does not eliminate) the possibilities of gaps.

I'm not sure that there is any swift or easy way to ensure no gaps in the sequence - scanning for MAX, adding one, and inserting that is probably the closest to secure, but is not recommended for performance reasons (and difficulties with concurrency) and the technique won't detect if the latest invoice number is assigned, then deleted and reassigned.

Can you account for gaps somehow - by identifying which invoice numbers were 'used' but 'not made permanent' somehow? Could an autonomous transaction help in doing that?

---

Another possibility - assuming that gaps are relatively few and far between.

Create a table that records sequence numbers that must be reused before a new sequence value is grabbed. Normally, it would be empty, but some process that runs every ... minute, hour, day ... checks for gaps and inserts the missed values into this table. All processes first check the table of missed values, and if there are any present, use a value from there, going through the slow process of updating the table and removing the row that they use. If the table is empty, then grab the next sequence number.

Not very pleasant, but the decoupling of 'issuing invoice numbers' from 'scan for missed values' means that even if the invoicing process fails for some thread when it is using one of the missed values, that value will be rediscovered to be missing and re-reissued next time around - repeating until some process succeeds with it.