Oracle Sequences

[charlie19102]

We make use of sequences throughout our application. However, one in particular is causing concern. The sequence is tied to a table and increments when an insert trigger fires on the table. The sequence then becomes the primary key ( number 15 ) on the inserted row. In creating the sequence, 1 was established as the increment value.

The number of rows in the underlying table is approximately 40,000; the sequence is over 216,000,000.

We see large jumps in the sequence ( 100k + ) only when new processes are started i.e. a batch job or online transaction. During this process, if rows are inserted, the increment is 1, as expected.

Nowhere in our application is the nextval statement on this sequence used except in the trigger.

Any ideas?

Thanks,
Charlie

[i2eye]

So you are saying that the sequence jumps more than 1 increment at a time?

If that is the case, perhaps the records are getting inserted and the sequence is being inserted as normal... but somewhere in the proccess, the inserts are not being commited...perhaps a rollback occuring, and only some of the records are being commited.

[saraths007]

the cache option of oracle sequence causes numbers to skip if there is any slight problem(can be network problems or any other)...So if its necessary in your application that sequence numbers are not skipped,use the no cache option...there wont be much decrease in performance also...


alter sequence "'sequencename' "no cache ;