most efficient: seq. num or max+1?

[ModelTrains]

I use Oracle 8.1.7 database and Java 1.3. I have several tables where the primary key is a single-column unique number.
From a database standpoint, which is more efficient:

1) using sequence numbers
2) using SQL MAX(primary_key) + 1

 

[swampBoogie]

You should never use

2) using SQL MAX(primary_key) + 1

as it may cause havoc with your data.

 

[sem]

I agree with swampBoogie. MAX(primary_key) + 1 is suitable only for single-user systems.

Regards, Dima

 

[AOLEURO]

Always try to use sequence numbers.

NEVER use MAX(xxx)+1 because Oracle have to make get max value from Primary Key Index. It is fast - but you drop of the CPU during operation.

Each time a session (process) need to do I/O the OS drop the process of the CPU during I/O because the process waits for data from disk. All OS do this because other processes can make a lot of CPU during I/O.

You also generates I/O on disks, so you use up channel I/O bandwidth.


Then you use Sequence, remember that CACHE (nocache) cache pre-numbers but you lose these pre-numbers then Instance is shutdown. If you want true ORDER of numbers remember to use ORDER during creating the sequence.

If you got a lot of rows in the table, then your Primary Key Index gets "wrong" in the long run. Because each insert always is at the last-most-right Index Key. You may need to rebuild your index and it is not always any help. Consider using REVERSE index (but you can't make range scan without full table/index scan).


Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[sem]

The drawback of using sequences (and particularly cached ones) is gaps in numbering due to uncommited transactions. So in some cases, when your customer need not only unique, but also sequential numbering, using MAX(primary_key) + 1 or creating extra table for storing this value is probably the only way.

Regards, Dima

 

[lewisp]

We always need to guarantee consecutive sequence numbers and therefore always use max + 1. Make sure to lock the table just before retreiving the next number, and also make sure you lock it for the shortest possible time.

 

[sem]

In the case you need consecutive numbers I'd recommend you to use a separate table to store a row with last number for each table. Thus you may lock only 1 record in this table and allow users updating other fields.

Regards, Dima

 

[sem]

And of course you do not need to select from potentially large table (or even index) to obtain last number. It's already here.

Regards, Dima

 

[Dagon]

I think it depends what you're doing. If this is for an OLTP application, then you would have to grab the latest sequence number and lock the table until you've finished the update. This could lock out other users and give very bad performance. For this purpose, sequences are obviously much better because they don't take out any locks (although you will get gaps, especially if the sequence uses a cache).

On the other hand, for an OLAP application, where you're doing a large data load, it would probably be better to read the max(sequence) once into a variable and keep incrementing it than use a sequence.

 

[ModelTrains]

This is an Intranet application that will have 50-100 users during peak times. Users will be inserting/updating one record at a time. The primary keys (with sequence numbers) are for internal use only and will not be visible to the users, thus they do not have to be sequential.

Thanks to EVERYONE for your answers and opinions! It's much appreciated. It looks like my solution will be to use sequence numbers.