• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

problem with the sequence generated by system

 
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tested to insert few rows into a mini table. but to my surprise, the "ID" increases like

2-->4-->6...

why doesn't it start from "1", and why does't it increment by 1 ?

I have already specified in SEQUENCE definition that it increment by 1, and nocache, enforce order, and enforce cycle. I also created a trigger like

BEFORE INSERT ON MINITABLE
for each row
begin
select minitable.nextval into: new.id from dual;
end;
/

Why the sequence id increment by 2 ??
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Raj Ohadi:
I tested to insert few rows into a mini table. but to my surprise, the "ID" increases like

2-->4-->6...

why doesn't it start from "1", and why does't it increment by 1 ?

I have already specified in SEQUENCE definition that it increment by 1, and nocache, enforce order, and enforce cycle. I also created a trigger like

BEFORE INSERT ON MINITABLE
for each row
begin
select minitable.nextval into: new.id from dual;
end;
/

Why the sequence id increment by 2 ??



Hi Raj,

When your sequence was created, it used the following syntax:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

The part relating to your question is the last clause: CACHE value.

The cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system fails or the database is stopped, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value. (Not really a good idea from a "if ain't broke, don't fix it" POV).

Using NOCACHE instead of "CACHE value" provides that none of the sequence values will be stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Paul, I think I turned off the cache, here is my setting

Min Value 1
Max Value 999999999999999999999999999
Increment By 1
Cycle Flag Y
Order Flag Y
Cache Size 0
Last Number 9

It looks bit strange because I use 10g XE GUI to set up sequence. But you can see cache size = 0.

so why it ALWAYS increments by 2 ?
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey Raj,

The minimum value you can use for CACHE is 2. If you use something smaller... it will still use 2.

Execute the following (after changing your_seq to the name of your sequence) and it should work the way you expect it to.

ALTER SEQUENCE your_seq NOCACHE;
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you very much Paul. The problem has been solved !
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic