I’m developing a system that allows to organise paper documents in folders. When folder is full, user hits ‘New folder’ button. This action calls nextval() of sequence of folders.
Here is how this table of documents looks like:
IDDOC FOLDER PARTNER USER 1 1 1341 56 .... 700 2 4532 56
The only problem is that gaps between folder numbers from sequence are pretty big.
Folder numbers I have now: 69, 89, 109, 129, 149, 169, 209.
What should I use instead of sequence to avoid it?
Advertisement
Answer
The main purpose of sequences is to provide a source of guaranteed unique identifiers. These are technical keys and generally speaking their actual value should be irrelevant. I mean – do you even need to show the folder number to the user?
If you’re that bothered use NOCACHE. That will minimise the gaps you’re seeing, and as long as you don’t create folders very often you won’t notice the performance hit from not caching a slack handful of numbers. You still may get gaps, if a transaction is rolled back or fails for any other reason but they should be infrequent; if they’re not you have bigger things to worry about than the numbering of your folders!
Other ways of generating a monotonically incrementing series are either cumbersome to implement (triggers over code control tables) or not guaranteed to be unique (using max(id)+1 in a trigger). If you want to use a code control table – that is a table which specifically tracks the last assigned folder ID – you should have a look at a previous answer I wrote which should how to do implement one. The one advantage of a code control table is that we can maintain counts with a group. So you could have a run of folder ids for each user, and increment them independently.