Re: Performance of sequences as primary key generators.

> 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 Received on Wed Jul 09 1997 - 00:00:00 CDT