Re: One sequence or many sequences?

On Jun 26, 10:53 am, Serge Rielau <srie..._at_ca.ibm.com> wrote:
> Mark D Powell wrote:
> > The practice of using a separate sequence for every table is wasteful
> > and unnecessary.  It is must more efficient to share a single sequence
> > among a collection of low insert rate tables.  This practice will
> > reduce the number of times Oracle has to flush a sequence from the
> > sequence cash to make room for another sequence
>
> Couple of thoughts on this:
> * If I have 100 sequences with cache 20 I run out of cache every 20
> NEXTVALs somewhere on average. If I have one sequence with cache 20 I
> run out of cache every 20 NEXTVALs just the same. The difference is in
> the memory requirement. And of course I can play with the CACHE size.
> * One sequence means bigger numbers. Bigger numbers mean more space
> (more digits).
> Perhaps that is negligible for a wide row, perhaps not.
>
> I think it boils down to mostly taste...
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Yes, it does boil down to a matter of "taste" or personal preference. However, remember this fact: sequences were invented to provide a fast scalable method of generating a numeric unique key. Where insert activity is low a sequence generator really is not needed for performance reasons. Also in a low insert environment it can take a long time to get to six digits (one million) so I suggest strongly encourging developers to whenever possible to reuse what alreadys exists rather than create another sequence. Otherwise the low use sequences are flushed from the sequence pool before reuse and more numbers are thrown away by the flushing than are used. Combining low use sequences actually can decrease the rate of usage in some cases and it reduces the stress on the sequence cache which can be benefical in a RAC environment. Still personal preference is a big part of the decision.

• Mark D Powell --