Sequence Increaser

[martindsouza]

Sometimes we need to increase a sequence number. Options include:

• dropping and recreating sequence with new start with value
• looping over sequence until hit desired number
• altering sequence to modify the increment by, selecting one value, then changing back to 1.

Need to think of all options and also factor in if it's an actively used sequence. In that case can't alter the increment by as someone may be selecting from it at the same time.

[jeffreykemp]

Regarding #3 (the preferred option).

I believe the usual use-case for advancing a sequence is not to advance up to an exact value, but to advance past one or more values that we don't want to get from the sequence. For example, for whatever reason we've already used certain PK values in the table and we need the sequence to skip past them.

The only risk is the following sequence of events:

1. We modify the increment by, e.g. +1000
2. The cache is flushed
3. Another session wants to get the next value, and it advances the sequence +1000
4. We advance the sequence, by +1000 again
5. We reset increment to +1.

So we're a bit unlucky and the sequence is advanced too far past the target value - big deal, though, right? It shouldn't matter too much. After all, sequences are not guaranteed gapless anyway, so a large gap that happens on occasion should not be cause for alarm.

[martindsouza]

@jeffreykemp I agree. What we might do is pass in a parameter to determine how we increase the sequence.

• Default would be Option 3 (above). I.e. alter sequence, next val, alter sequence
• The the other option would loop through until we hit the increase. Not great performance wise but solves the issue that you highlighted.

[DominiqueComte]

I think modifying a sequence on a RAC database with at least 2 nodes would need a few tests, to be sure of how it behaves if the cache value is > 1.