Re: One sequence or not?

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Wed, 06 Nov 2002 16:35:32 GMT
Message-ID: <3DC944C6.9B1F49ED@exesolutions.com>

Martin Doherty wrote:

> Hmmm, might be some resource contention there (exactly the problem
> sequences were invented to solve, but if all processes are hitting the
> same sequence I can see it being slower (by a little? a lot? :-\ ) than
> using many sequences, each with its own little cache of ready numbers.
> You'd probably want to use a BIG sequence cache to reduce the number of
> sequence-related I/Os.
>
> One common scenario in a system migration is to migrate legacy data
> (including primary and foreign keys), then start the numbering of the
> applicable sequences above the high water mark of the migrated data. I
> suppose you could still have a single sequence, and start its numbering
> above the maximum high water mark of all migrated keys, but you may not
> achieve your goal of having universally unique key values unless the
> migrated data also obeyed this restriction. Also bear in mind the
> possibility of future unforeseen migrations due to corporate takeovers etc.
>
> Hashim, anyone needing contiguous numbering should NEVER use a sequence
> for that purpose. Sequences are unable by nature to guarantee no gaps
> between committed key values. Any transaction that grabs a nextval and
> then issues a rollback will cause a gap, regardless of whether you are
> using one centralized sequence or one sequence per table.
>
> FYI, Oracle Applications uses one dedicated sequence per surrogate key
> column. I don't know the reasoning behind that choice but we are talking
> about one of the most complex bodies of application code on the planet.
> The typical naming convention is <table_name>_S.
>
> hth
> Martin
>
> M Hashim wrote:
>
> >It's good on the maintenance side, for not having to maintain numerous
> >sequences. In an object oriented environment, it's usually a single
> >identifier, OID. It's simple and effiecient from a coding perspective.
> >
> >The negative, should you require continuous record identifier, having a
> >single sequence will NOT give you continuous numbering. It will create gaps.
> >In some financial institutions, having gaps may be a problem Think about it,
> >from an auditing perspective.
> >
> >
> >"Richard Kuhler" <noone_at_nowhere.com> wrote in message
> >news:IEUx9.63856$X9.22712785_at_twister.socal.rr.com...
> >
> >
> >>I'm working on a project where they've used ONE Oracle sequence to
> >>generate surrogate keys for all rows in all tables. This database will
> >>be used for ad hoc queries and reports. Since an ID will only appear in
> >>one table, the reasoning is that any incorrect joins will result in no
> >>data being returned rather than incorrect data.
> >>
> >>Opinions? Experiences?
> >>
> >>
> >>Thanks,
> >>Richard Kuhler
> >>
> >>
> >>
> >
> >
> >
> >

I'll take issue on one very small point. A rollback does not necessarily mean a gap in the sequence and a rollback has no affect on the number the sequence generated. In systems with audit requirements I often do something like the following:

BEGIN
   SELECT seq_gl.NEXTVAL
   INTO i
   FROM dual;

   INSERT INTO gl_table
   (gl_id, somevalue)
   VALUES
   (i, somevalue);

   COMMIT;
EXCEPTION
   WHEN OTHERS THEN

      INSERT INTO gl_errors   -- a table with no constraints
      (gl_id, somevalue)
      VALUES
      (i, somevalue);

END; Not only is the number not lost ... the transaction isn't either. And the audit quality is improved.

Daniel Morgan Received on Wed Nov 06 2002 - 10:35:32 CST