Originally posted by Raj Ohadi: I tested to insert few rows into a mini table. but to my surprise, the "ID" increases like
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 ??
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.