Bernard
Bernard

Reputation: 4580

How to increment sequence only by 1?

First I run all these in java with jdbc driver...

Here I define a table:

create table HistoryCCP(
  ID     NUMBER(6)      NOT NULL,
  SCRIPT VARCHAR2(1000) NOT NULL
 )

Here I define a sequence:

CREATE SEQUENCE SYSTEM.HistoryId  
   MINVALUE  1 
   MAXVALUE 1000000 
   INCREMENT BY 1 
   START WITH 1  
   NOORDER   
   NOCYCLE

Now I insert to table by using this here:

insert into HistoryCCP 
  values (SYSTEM.HistoryId.nextval ,'HELLOOOO  ')

Whenever I close the program and run it again and try to insert, it increments it by ten! And when I defined sequence like this:

CREATE SEQUENCE SYSTEM.HistoryId  
   MINVALUE  1 
   MAXVALUE 1000000 
   INCREMENT BY 1 
   START WITH 1
   CACHE 100       -- added cache parameter
   NOORDER   
   NOCYCLE

It increase it by 100! Do you know why it behaves like this and how to increment it by 1?

Upvotes: 1

Views: 2137

Answers (3)

dbra
dbra

Reputation: 631

NOCACHE would work, but also would be a bad idea under for many reasons, and a total nonsense if you plan to bring your application on a Oracle RAC.

Oracle Sequences are for (internal) unique ID, not for strictly progressive number imposed by requirements. As example, let's say that using a sequence for generating the classical "protocol number" is a common flaw of many financial accounting software: looks easy when beginning but when the project grows it kills you.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52346

Never rely on sequences for gap free numbering.

The cache value is the number of sequence values that are held in memory by the database server to avoid the need to keep updating it's internal $SEQ table with the most recently used value. If you reduce the cache value then you increase the rate at which the $SEQ table has to be modified, which slows the system.

Cached values can be aged out, and are lost on system restart, and values are not reused if a transaction gets rolled back.

The presence of gaps should not be a problem for you -- if it is then you'll need to use something other than a sequence to generate the numbers, and doing so will serialise inserts to that table.

Upvotes: 6

BN0LD
BN0LD

Reputation: 52

Try to use NOCACHE options for sequence.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm

Upvotes: 0

Related Questions