Sequential ID column without gap! – Keep Learning Keep Living

Hello,

A customer is asking to you generate order numbers one by one and without gap! What an unpleasant request isn’t it? if you are not familiar to databases then you might think that this request is logical but it is not.

So your customer asked for this and you decided to develop your code based on this. How this can be achieved? You can count the number of current orders and then increase it by one and use this number as new order number but after a while you will realize that customer is complaining about a “unique constraint” error (I hope they will otherwise this means you don’t have a unique key on order number and you will create duplicate orders with same number).

--pseudo code
... get order data
select count(*) into X_Number_of_Orders from Orders;

X_Number_of_Orders := X_Number_of_Orders +1; -- New Order Number

insert into Orders (Order_Number,...) values (X_Number_of_Orders, ...);
commit;

--pseudo code

... get order data

select count(*) into X_Number_of_Orders from Orders;

X_Number_of_Orders := X_Number_of_Orders +1; -- New Order Number

insert into Orders (Order_Number,...) values (X_Number_of_Orders, ...);

commit;

When you check the situation you will find out that these orders’ insert times are very close to each other. Basic problem here is when 2 people tries to insert a new order at the same time, you will get count of the rows (at the same time) and both session will get same number of rows and increase it by one and use it as order number but whichever session inserts the order row, the other one will get and unique constraint error (hopefully). so getting count of rows and increasing it does not guarantee that you will get a unique ID!

After that point some people understand what they are trying to do and make some research and stop themselves. Some other people will try to overcome this “obstacle” and come up with new ideas(!). one of them is adding a “for update” clause to select count(*)… statement so no one can run this select statement at the same time. This terrible idea will destroy your db performance and cause too much locks, wait events etc because for update will lock all rows that you select (in this case all rows in table) so it won’t be able to insert more than one order at the same time and also, a user who is trying to update an old row will also wait for completion of new order’s insert operation. so this is the worst solution. (it is not even a solution!)

a Second group of people are create a ORDER_NUMBER table and insert a rows in it to keep order number.

-- table definition
-- create table order_numbers(ID number);

--pseudo code 
... get order data 

select ID into X_Number_of_Orders from Order_Numbers for update; 
X_Number_of_Orders := X_Number_of_Orders +1; -- New Order Number 

insert into Orders (Order_Number,...) values (X_Number_of_Orders, ...); commit;

-- table definition

-- create table order_numbers(ID number);

--pseudo code

... get order data

select ID into X_Number_of_Orders from Order_Numbers for update;

X_Number_of_Orders := X_Number_of_Orders +1; -- New Order Number

insert into Orders (Order_Number,...) values (X_Number_of_Orders, ...); commit;

again they have to use a “For Update” clause to make sure no one is reading the table at the same time and every number will be unique! This solution (?!) will save you locking from all orders in orders table so the sessions which are trying to update a row will continue their job but sessions which are trying to insert orders at the same time have to wait each other. if this is a busy system (Let’s say 100.000 order per day) then wait on Order_Numbers table will be huge and your customer(s) will be complaining about slow processing.

Then what is the solution here? Solution is giving up to generate gapless sequential numbers. Gapless ID columns are nice for human eyes but certainly not needed for a software system. Since we have a limited capacity to remember, we want things in some specific order so we can track it but computer systems are capable of tracking billions of data without “forgetting” staff. Simply just use a SEQUENCE for Oracle (every db has an object for this purpose). Sequence is an object that you can query simultaneously and it guarantees that every number will be unique (unless you told it to do otherwise) whoever you are and whenever you asked for it. if so, can not we use sequences for our gapless ID column? not exactly.

Mostly we create sequences with a CACHE option. whenever you ask a new number from a sequence it will generate 20 numbers by default. this will increase the performance of the sequence but if you shutdown your database cached numbers will be gone! after opening database you won’t get those numbers back. so there will be a gap! Secondly, let’s say you get a number from sequence but before inserting your row you got an error and your transaction rollbacked. That number is also lost! again there is a gap.

As I said earlier, gapless (and fully sequential ) order numbers are a fantasy (mostly!) if my customer(s) force me to do this I would design my system with IDs which are generated from sequences and simply add a “rownum” or “row_number…” function while showing those rows to my customer so they can see their rows with and “Ordered” ID. of course that will bring some extra coding to you because when customer clicked a row to see the details you must be holding real ID and fetch data accordingly but this will save you from so much headache (locks, wait events etc).

In this case you might ask that what will happen if someone deletes a row from the table! Let’s say I have 5 rows and I used a row_number analytic function to generate fake IDs and then someone delete the second row so on the next time I will be showing 4 rows and 5th row with fake ID will be shown as 4 now. Please don’t forget for this kind of problems you wouldn’t be using DELETE anyway. even if you generate your IDs ordered and gapless, and if someone deletes a row the you will have a gap which you already don’t want that. so no one should be running delete otherwise there is no point requesting “gapless sequential ID”.

I defined this request as “fantasy” before but this is not entirely true. There might be some legal issues here for example your government might force you to keep Invoice Numbers ordered and fully sequential. So you might not have any option but accept this request. Or your customer might declared that they won’t work with you unless you do that! So to keep your customer you must do it and since you already developed your system with sequences you might not have enough time or budget to convert your software to a “rownum or row_number” fake id solutions.

Note: by saying “fully sequential”, an order’s number can not be bigger from the order that inserted after that row. you have 5 rows and you started to insert them (one row for every minute), their numbers must be: 1,2,3,4 and 5. they can not be 1,2,3,5 and 4 both gapless but not fully sequential.

One of my customer asked me a solution about that kind of problem and they asked it with worse conditions: Every customer’s order number must be gapless and fully sequential. As this condition is not enough, these numbers must be reset to 1 for every year. they want to show order numbers as:

2020-1
2020-2
2020-3
….
2021-1
2021-2 etc

Since they have more than one customer, they want these conditions per customer. every customers’ order number must be start with 1 and it must be reset every year. How nice…

still I don’t want to use “storing number into a table and selecting it with for update” solution. So I come up with a sequence solution has a little bit low performance (I won’t use sequence caching) and relatively complicated.

Create Or Replace Package Pkg_Special_ID_Generator As 
  Function Sf_Get_Id(P_Customer_ID Number) Return Number;
End;
/

Create Or Replace Package Body Pkg_Special_ID_Generator As 
  E_Sequence_Does_Not_Exist Exception;
  Pragma Exception_Init(E_Sequence_Does_Not_Exist, -2289);
  
  E_Sequence_Already_Exist Exception;
  Pragma Exception_Init(E_Sequence_Already_Exist, -00955);

  X_Current_Year Varchar2(4) := To_Char(Sysdate, 'yyyy');
  X_Sequence_Prefix Varchar2(128) := 'SEQ_CUSTOMER_ID_' || X_Current_Year || '_';  
  
  Procedure Sp_Create_Sequence(P_Customer_ID Number) As
    Pragma Autonomous_Transaction;
    X_Current_Sequence_Name Varchar2(128) := X_Sequence_Prefix || To_Char(P_Customer_ID);
  Begin
    Execute Immediate 'create sequence ' || X_Current_Sequence_Name || ' nocache'; 
    Commit;
  End;
  
  Function Sf_Get_Id(P_Customer_ID Number) Return Number As
    X_Result Number;
    X_Current_Sequence_Name Varchar2(128) := X_Sequence_Prefix || To_Char(P_Customer_ID);
  Begin
    Execute Immediate 'select '|| X_Current_Sequence_Name || '.nextval from dual' Into X_Result;
    Return X_Result;
  Exception 
    When E_Sequence_Does_Not_Exist Then
      Begin
        Sp_Create_Sequence(P_Customer_ID);
      Exception
        When E_Sequence_Already_Exist Then
          null;
      End;
      Return Sf_Get_Id(P_Customer_ID);
  End;
End;
/

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;
----------------------
1

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;
----------------------
2

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;
----------------------
3

Select Pkg_Special_ID_Generator.Sf_Get_Id(11) From Dual;
----------------------
1

Select Pkg_Special_ID_Generator.Sf_Get_Id(11) From Dual;
----------------------
2

select sequence_name, increment_by, cache_size, last_number from user_sequences;
----------------------
SEQ_CUSTOMER_ID_2020_10	1	0	4
SEQ_CUSTOMER_ID_2020_11	1	0	3

Create Or Replace Package Pkg_Special_ID_Generator As

Function Sf_Get_Id(P_Customer_ID Number) Return Number;

End;

Create Or Replace Package Body Pkg_Special_ID_Generator As

E_Sequence_Does_Not_Exist Exception;

Pragma Exception_Init(E_Sequence_Does_Not_Exist, -2289);

E_Sequence_Already_Exist Exception;

Pragma Exception_Init(E_Sequence_Already_Exist, -00955);

X_Current_Year Varchar2(4) := To_Char(Sysdate, 'yyyy');

X_Sequence_Prefix Varchar2(128) := 'SEQ_CUSTOMER_ID_' || X_Current_Year || '_';

Procedure Sp_Create_Sequence(P_Customer_ID Number) As

Pragma Autonomous_Transaction;

X_Current_Sequence_Name Varchar2(128) := X_Sequence_Prefix || To_Char(P_Customer_ID);

Begin

Execute Immediate 'create sequence ' || X_Current_Sequence_Name || ' nocache';

Commit;

End;

Function Sf_Get_Id(P_Customer_ID Number) Return Number As

X_Result Number;

X_Current_Sequence_Name Varchar2(128) := X_Sequence_Prefix || To_Char(P_Customer_ID);

Begin

Execute Immediate 'select '|| X_Current_Sequence_Name || '.nextval from dual' Into X_Result;

Return X_Result;

Exception

When E_Sequence_Does_Not_Exist Then

Begin

Sp_Create_Sequence(P_Customer_ID);

Exception

When E_Sequence_Already_Exist Then

null;

End;

Return Sf_Get_Id(P_Customer_ID);

End;

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;

----------------------

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;

----------------------

Select Pkg_Special_ID_Generator.Sf_Get_Id(10) From Dual;

----------------------

Select Pkg_Special_ID_Generator.Sf_Get_Id(11) From Dual;

----------------------

Select Pkg_Special_ID_Generator.Sf_Get_Id(11) From Dual;

----------------------

select sequence_name, increment_by, cache_size, last_number from user_sequences;

----------------------

SEQ_CUSTOMER_ID_2020_10 1 0 4

SEQ_CUSTOMER_ID_2020_11 1 0 3

My package generates IDs for your customers. Sf_Get_Id function gets the customer ID as parameter and check for a specific sequence name pattern: SEQ_CUSTOMER_ID_<YEAR>_<CUSTOMER_ID> if it is not exist (this might be the first time you use the package or it might be the first call of the new year) it creates that sequence. as a con if you have many customers there will be many sequences and still there is a gap probability because you might get the ID but not use it ( you might get an error before insert your ID) but if some small gaps (a few per year) is acceptable then I would go with this solution (?!).

As I said a real solution would be using sequence (with caching and all the staff) and generate a fake ID per rows. I wouldn’t go with any of those unless I have to do it because of the law or something.

PS: whoever user will run this code have to have “create sequence” privilege directly not over a role or something.

thanks.

Leave a Reply Cancel reply