Re: One sequence or many sequences?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 26 Jun 2008 07:20:00 -0700 (PDT)
Message-ID: <23f09bf6-1f5b-4bca-bce1-821d03eeb8de@w7g2000hsa.googlegroups.com>


On Jun 25, 10:53 pm, Michael Austin <maus..._at_firstdbasource.com> wrote:
> Jack.Lee wrote:
> > I have many tables which need sequences.I think there are two
> > solutions.One is that I create each sequence for each table.The other
> > is that I create only one sequence for many tables.Which is the better
> > one(or another solution)?And Why?
>
> > Thanks in advance!
> > Jack.Lee
>
> You need to ask yourself the right question.  It isn't one or many
> sequences, the question is do these sequences need to be sequential or
> not on any of the tables.  Depending on the workload, one may suffice if
> they do not need to be relatively sequential.  If it is a heavy
> workload, you may need one for each regardless of the need to be
> sequential.
>
> See:http://www.psoug.org/reference/sequences.html

Sequence values are always sequential in nature, but the values may not be consecutive. Under relational design theory a generated key does not have any meaning; It exists just to identify and associate row data together. Oracle sequence values are not guaranteed to be consecutive nor is there any generally any genuine business reason why keys must be consecutive.

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

HTH -- Mark D Powell -- Received on Thu Jun 26 2008 - 09:20:00 CDT

Original text of this message