Re: Performance of sequences as primary key generators.

In article <19970709172601.NAA13951_at_ladder01.news.aol.com>, TommCarr <tommcarr_at_aol.com> writes
>> A sequence, as I understand it, is actually just another table that is
 being
>> incremented by a procedure that is run when the NEXTVAL statement is
 issued.
>> Is it correct to say that the following are issued when a sequence is
 utilized
>> to generate a primary key?
>>
>> 1) The original "INSERT" statement is issued to 'emp' table.
>> 2) The SQL is stored in the SGA while
>> 3) A trigger is called which
>> 4) Performs a "SELECT" from the emp.sequence table and
>> 5) Performs an "UPDATE" to the emp.sequence table and
>> 6) Writes the NEXTVAL value to the emp table and THEN
>> 7) The original SQL is executed, involving yet another write.
>>
>> I realize this is crude, but is it roughly correct? This seems like a
 lot of
>> overhead. Does anybody have any input on this? I am a beginner, and am
 very
>> open to new information.
>>
>> If there is another way to generate primary keys for a table, I would
 love to
>> hear it.
>>
>Read up on sequences (Oracle Server Application Developer's Guide) and you
>will see that the values are buffered in memory (and you control how many
>when you create the sequence). That is why when two different users
>access the same sequence, the values they obtain are guaranteed to be
>unique, but not necessarily consecutive. Having converted all our
>application here from our own table-based vaues to sequences, I can assure
>you that the overhead of using sequences are inconsequencial.
>(InconSEQUENCial...Get it?)
>
>Tomm Carr
>In giving freedom to the slave we assure freedom to the free,
>honorable alike in what we give and what we preserve.
>---- Abraham ByGod Lincoln

Its best NOT to view a sequence as being yet another table.

When the NEXTVAL function is performed on a sequence there is no turning back. ie you can't rollback from it. Any other transaction that does a NEXTVAL on the same sequence is guaranteed to get a separate value even though the first transaction hasn't yet been commited.

Sequences were introduced to simplify the generation of Primary keys.

The mechanism for managing sequences is actually outside of the scope of any transaction that "selects NEXTVAL" from the sequence. Therefore your sequence of events described above is not strictly true.

As TOM said, the overhead in using sequences is inconsequential & not worth worrying about. From your point of view, whenever your code/trigger or whatever selects NEXTVAL from a sequence in a bid to use the value as a Primary key on a table, whatever value you get is guaranteed to be unique. You don't need to rush your transaction hoping no one else selects NEXTVAL from the same sequence (before you commit).     

-- 
Bola Ogunlana