SQL Snippets ™: Tutorials for Oracle Developers

This tutorial discusses traps that can be encountered when the NEXT_SERIAL_NUMBER() function created in Serial Number Generator is misused in UPDATE statements. The proper technique for using the serial number generator with UPDATE statements is described in Generator With UPDATE.

Before proceeding however, let's clean up old test data from prior tutorials and create some fresh test data.

Test Data Setup

delete from serial_number_controls ;

insert into serial_number_controls
  ( key, last_serial_number, increment_size )
values
  ( 'T.SERIAL_NUMBER' , 0 , 1 )
;

commit;

delete from t;

insert into t ( key, name, serial_number )
select 100 + level as key, 'x' as name, null as serial_number
from   dual
connect by level <= 1000 ;

commit;
 

(See Integer Series Generators: CONNECT BY LEVEL Method for an explanation of the connect by level <= 1000 clause.)

select key, serial_number
from   t
where  key between 101 and 105
or     key between 1096 and 1100
order by key ;
 

       KEY SERIAL_NUMBER
---------- -------------
       101
       102
       103
       104
       105
      1096
      1097
      1098
      1099
      1100
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 0              1
 

Using NEXT_SERIAL_NUMBER() with ROWNUM

Using NEXT_SERIAL_NUMBER() and ROWNUM together in an UPDATE statement will lead to gaps in the series and a wrong value in SERIAL_NUMBER_CONTROLS.LAST_SERIAL_NUMBER.

update t
set    serial_number = NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' ) + ROWNUM - 1
where  serial_number is null
and    key between 100 and 110 ;

10 rows updated.


 

select key, serial_number from t where key between 100 and 110 order by key ;
 

       KEY SERIAL_NUMBER
---------- -------------
       101             1
       102             3
       103             5
       104             7
       105             9
       106            11
       107            13
       108            15
       109            17
       110            19
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                10              1
 

rollback;
 

Using NEXT_SERIAL_NUMBER() without ROWNUM

While it is possible to call NEXT_SERIAL_NUMBER() without ROWNUM in an UPDATE statement and still get accurate results, from a performance standpoint this approach is worse than the solution in Generator With UPDATE Commands, as the statistics in the following simulations show.

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

set autotrace on statistics

update t set
  serial_number =
    NEXT_SERIAL_NUMBER( 'T.SERIAL_NUMBER' )
where
  serial_number is null
;

Statistics
---------------------------------------------------
       1036  recursive calls
       5963  db block gets
       2002  consistent gets
          0  physical reads
     841528  redo size
        918  bytes sent via SQL*Net to client
       1022  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed


set autotrace off

--

-- check the results

select key, serial_number
from   t
where  key between 101 and 105
or     key between 1096 and 1100
order by key ;

       KEY SERIAL_NUMBER
---------- -------------
       101             1
       102             2
       103             3
       104             4
       105             5
      1096           996
      1097           997
      1098           998
      1099           999
      1100          1000


 

variable v_next_serial_number number

begin
  :v_next_serial_number :=
    next_serial_number( 'T.SERIAL_NUMBER' );
end;
/

set autotrace on statistics

update t set
  serial_number =
    :V_NEXT_SERIAL_NUMBER + ROWNUM - 1
where
  serial_number is null
;

Statistics
---------------------------------------------------
          4  recursive calls
       3209  db block gets
        899  consistent gets
          0  physical reads
     444612  redo size
        920  bytes sent via SQL*Net to client
       1054  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed


set autotrace off

-- see Note 1

-- check the results

select key, serial_number
from   t
where  key between 101 and 105
or     key between 1096 and 1100
order by key ;

       KEY SERIAL_NUMBER
---------- -------------
       101             1
       102             2
       103             3
       104             4
       105             5
      1096           996
      1097           997
      1098           998
      1099           999
      1100          1000

 

Note 1: Normally the SERIAL_NUMBER_CONTROLS table is updated at this point. This simulation omits that step to allow us to use AUTOTRACE.

See SQL*Plus® User's Guide and Reference - Tuning SQL*Plus - Statistics for a description of the statistics shown above.