Serial Numbers

Precreated Serial Numbers

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11899.html]SQL Snippets: Serial Numbers - Precreated Serial Numbers[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11899.html">SQL Snippets: Serial Numbers - Precreated Serial Numbers</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Serial Numbers - Precreated Serial Numbers
  • URL (href): http://www.sqlsnippets.com/en/topic-11899.html