## Serial Numbers

### Generator With UPDATE - Gotchas

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.

Not So Good
UPDATE + NEXT_SERIAL_NUMBER()
Better
Bind Variable + UPDATE
```--
--
--
--
--
--
--

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
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
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.

#### 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-11901.html]SQL Snippets: Serial Numbers - Generator With UPDATE - Gotchas[/url]