Oracle sequence or use application code

[olmos]

Is there any performance issues in using Oracle sequences to create a
auto generated primary key for my tables or is it better to
create the autonumber by using the application code instead which I'm
using - Cold Fusion.

Thanks,
olmos..

 

[Swany]

You shouldn't have any problem whatsoever using a sequence for the primary key of your table.

If you expect a very high transaction rate, you may need to play with the cache values on the sequence.

 

[sem]

As Swany've mentioned above it's much better to use a sequences with cache in a high transaction rate.
One more reason for using them is possible running on Oracle Parallel Server where they provide a considerable performance gain.

 

[bookouri]

if you were doing an employee application for example. Most places might only add a few dozen employees a year. You can generate your employee numbers "on the fly" by doing a select max(idnumber) to get the last number and generate the new numbers that way. You can afford the time it takes to do the select max(). But if you're generating hundreds of receipts in a day and building up thousands of receipt numbers you couldn't afford to do a select through thousands of records, hundreds of times a day, the performance would be unacceptable. You would want to use the sequence number for that kind of application.

 

[layco]

If your application is going to go into database replication you will need to use a guid (globally unique identifier) instead of a sequence for your primary key. This will allow replication of your table to databases at other locations. In general I think replication is pretty much inevitable for all robust applications and we should start using quids instead of sequences everywhere. I havn't used them enough to evaluate their impact on performance in high transaction environments.

I would never generate primary key values in code. If you do, your table is linked to your code segment because you can't do inserts without it. This makes it much harder to move the database to a new platform or to write other applications that use the same table.


Here is a clip from the Oracle documentation:

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier and a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

Example
The following examples return the 32-character hexadecimal representation of the 16-byte raw value of the global unique identifier:

CREATE TABLE mytable (col1 VARCHAR2(10), col2 RAW(32));
INSERT INTO mytable VALUES ('BOB', SYS_GUID());
SELECT * FROM mytable;

COL1 COL2
---------- --------------------------------------------------
BOB 5901B85D996C570CE03400400B40DCB1

SELECT SYS_GUID() FROM DUAL;

SYS_GUID()
--------------------------------
5901B85D996D570CE03400400B40DCB1