SQL Snippets ™: Tutorials for Oracle Developers

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.