But Oracle does have sequences, which are very handy indeed for this kind of thing. In fact, I generally like sequences better than auto_increment fields, because they're not so magical, and give lots o' control. You should definitely investigate them, if you haven't.
| [reply]
[d/l] |
I agree with you on the ease of using Oracle sequences. But I am a bit annoyed when Oracle sequences tend to leave 'holes' in the sequence number, when a process dies after fetching the sequence, but before inserting into the table, for example. So I tend to use a trigger on insert instead to simulate a contineous auto sequence.
| [reply] |
The fact that Oracle (Sybase does too) leaves "holes" has all
to do with performance. With properly written SQL, it's not
necessary that the sequence is continuous, as long as the
elements are unique (so you can use them as a unique key), and monotonous (so you can do an 'order by' on them). I wouldn't use a trigger
for that if performance mattered.
Abigail
| [reply] |
If the 'holes' are a problem, and like Abigail-II says in a well designed system they shouldn't be, set the cache size to 0,
create sequence no_holes nocache;
be warned: performance will be terrible wrt to cached sequences. | [reply]
[d/l] |