Re: One sequence or not?

If you wanted unique non-overlapping values and were concerned about the resource implications of using just one sequence you could always just create non-overlapping sequences.

E.g. Say you have 150 different id's you want to be sequenced and distinct sets, then you could

create sequence tab1_seq start with 1 increment by 1000; create sequence tab2_seq start with 2 increment by 1000; ...
create sequence tab150_seq start with 150 increment by 1000;

Of course this also has obvious limitations...

Adrian

In message <mcXx9.15$E23.169_at_news.oracle.com>, Martin Doherty <martin.doherty_at_oracle.nospam.com> writes
>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
>>>
>>>
>>
>>
>>
>

-- 
Adrian Carlson-Hedges