in reply to Re: Re^2: Message Reply Sequencing
in thread Message Reply Sequencing

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.

Replies are listed 'Best First'.
Re: Re: Re: Re^2: Message Reply Sequencing
by Roger (Parson) on Dec 03, 2003 at 06:21 UTC
    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]
        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]
      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]

In Section Seekers of Perl Wisdom