An alternative to the serial number generation technique presented in Serial Number Generator involves precreating a large number of rows with serial numbers already assigned. By doing this the scalability bottleneck associated with serial numbers is only encountered with the infrequent transactions that create a new batch of rows.
This approach can be implemented a number of ways. One way works like this. Say we had an API packaged procedure called "T2_API.NEW_ITEM" that adds new items to table T2 . When table T2 is empty calling T2_API.NEW_ITEM for the first time creates the first batch of rows and allocates the first row in the batch to the new item.
select * from t2 order by key ; no rows selected
declare v_key t2.key%type ; begin v_key := T2_API.NEW_ITEM( 'A' ); commit; end; /
select * from t2 order by key ;
KEY NAME STATUS SERIAL_NUMBER ---------- ----- ---------- ------------- 1001 A Assigned 1 1002 I1002 Available 2 1003 I1003 Available 3 1004 I1004 Available 4 1005 I1005 Available 5
Subsequent calls to NEW_ITEM use up existing, available rows.
declare v_key t2.key%type ; begin v_key := T2_API.NEW_ITEM( 'B' ); v_key := T2_API.NEW_ITEM( 'C' ); commit; end; /
select * from t2 order by key ;
KEY NAME STATUS SERIAL_NUMBER ---------- ----- ---------- ------------- 1001 A Assigned 1 1002 B Assigned 2 1003 C Assigned 3 1004 I1004 Available 4 1005 I1005 Available 5
After the last available row is used a new batch is added to the table.
declare v_key t2.key%type ; begin v_key := t2_api.new_item( 'D' ); v_key := T2_API.NEW_ITEM( 'E' ); -- last available row used v_key := T2_API.NEW_ITEM( 'AA' ); -- new batch created by this call v_key := t2_api.new_item( 'AB' ); v_key := t2_api.new_item( 'AC' ); commit; end; /
select * from t2 order by key ;
KEY NAME STATUS SERIAL_NUMBER ---------- ----- ---------- ------------- 1001 A Assigned 1 1002 B Assigned 2 1003 C Assigned 3 1004 D Assigned 4 1005 E Assigned 5 1006 AA Assigned 6 1007 AB Assigned 7 1008 AC Assigned 8 1009 I1009 Available 9 1010 I1010 Available 10
The source code for the T2_API package is available in the next page, Precreated Serial Numbers - API Code.