Re: One sequence or not?

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
>>
>>
>>
>
>
>
>
Received on Tue Nov 05 2002 - 15:57:04 CST