Re: Re: Re: Re^2: Message Reply Sequencing

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.

Comment on Re: Re: Re: Re^2: Message Reply Sequencing

Replies are listed 'Best First'.
Re: Message Reply Sequencing by Abigail-II (Bishop) on Dec 03, 2003 at 10:05 UTC
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]
Re: Re: Message Reply Sequencing by mpeppler (Vicar) on Dec 05, 2003 at 16:25 UTC
You can minimize the holes in the sequence for Sybase by using the `identity_gap` attribute, available in 12.0 and later releases of Sybase: `create table foo ( id numeric(9) identity, .... ) with identity_gap = 1` [download] This would limit the potential gap in the sequence to 1, at some performance cost in the identity value generation. Michael	[reply] [d/l] [select]
Re: Re: Re: Re: Re^2: Message Reply Sequencing by rdfield (Priest) on Dec 03, 2003 at 11:31 UTC
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;` [download] be warned: performance will be terrible wrt to cached sequences. rdfield	[reply] [d/l]