Thursday, February 23, 2006
Robert Vollman talks about Oracle sequences in his blog. Basically there are 3 potential issues with Oracle sequences that we need to consider when doing db design:
1. The squence number may "jump", that is 1,2,3,5,6,8,... with number 4 and 7 missing. One reason is if you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started.
2. By default, the sequence caches the 20 next values for faster retrieval. So if there is a system failure, we will lost the cached values upon system recovery. Thus you end up with a gap of upto 20. One way to avoid this is to use NOCACHE command when creating the sequence. And of course you lose the benefit cache brings as well.
3. Gary Myers also pointed that the sequence number can only grow upto the column's data size. For example, if we have column as number(6), then the max value will be 999999.