I'm using sequence to generate autoincrement id in Oracle. And I have a trigger to pull number from the sequence every time before insert.
CREATE SEQUENCE "my_SEQ" MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 1 CACHE 5000 NOORDER NOCYCLE;
And I found that there're 5000 (same as cache) number missing. For example, my first try gave me id 1 and when I came back later, the second insert generated an id of 5001. Oracle document says "If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. " Can anyone explain what's going on here? Thanks in advance.
When new value is pulled from a sequence, DB need to write this information to disk, so if there is a failure - there would be no collision after restart.
It is uneficient to wrtie EVERY number to disk.
To address that Oracle can cache values, so when you requesting one value, DB will pull some predefined set of numbers (5000 in your case) and write that information to disk. These values are never going to be reused if there is a DB failure.
You also specified NOORDER. That means that you are OK to get numbers out of sequence. It is more eficient than using ORDER.
Additional materials: