Why do you hate sequences on Oracle?

Question

Some people don't like sequences on Oracle. Why? I think they are very easy to use and so nice. You can use them in select, inserts, update,...

Answer 1

Because they're harder to use than SERIAL columns in IBM Informix Dynamic Server.

Answer 2

To sum it up, I like sequences, but I'd like an autoincrement keyword for columns even more.

Answer 3

I used to prefer auto increment columns (ala MySQL, SQL Server and others) until I got heavily into JPA. At that point the weakness of auto incrementing fields became apparent to me: you have to insert before you can get an ID. This is a problem with maintaining relationships between objects.

In JPA when using Oracle, as soon as you do entityManger.persist(object) it selects the next sequence value and assigns it as the ID whereas with auto-incrementing columns that doesn't happen until after a commit. Big difference.

They are a bit harder work though, which I guess is why people don't like them (or they don't see the advantages compared to auto-incrementing fields).

The other problem with sequences is that they're only loosely ordered and a lot of people want absolute ordering. That I think is the biggest downside (that I can see anyway).

Answer 4

Because I've been bitten several times by DBAs migrating databases, moving all the object and data, and recreating the sequence incorrectly, restarting it from 0. Hilarity ensues...

Also sequences can throw one for a loop on RAC, where unless you specify that you want them to be strictly increasing, you will get unique numbers out of them -- but not necessarily in strictly increasing order (this happens because in order to avoid inter-node communication for every sequence.nextval call, each node gets a separate small slice of the upcoming numbers). Wreaks havoc on various "select max(sequence_id)" queries.

Oh and the autoincrement keyword is nice, but that's just syntactic sugar -- the other two issues are pretty serious "gotchas".

Answer 5

Some colleagues didn't like them because it was hard to remember the boilerplate to set up a plain vanilla autoincrement field.

Fortunately SQL Developer will fill in the boilerplate for you, so now the issue is not so bad.

Answer 6

I don't hate sequences. Sequences are fantastic. Love them!

They're safe in a distributed environment; they can be used to mimic an autoincrement field where required (with a trigger) while also letting you get the ID beforehand - which is great when you're preparing datasets to load into multiple tables and you want the ID before you insert.

I take on board Jonathan Leffler's comment to his own answer, but - for me - the balance of the control a separate sequence gives you over ID generation with the relative ease of mimicking an AUTOINCREMENT field works.

Answer 7

An issue with sequences is that if you have multiple workers writing to the same table, all getting their key from the same sequence, the result is that there will be contention around the index blocks, with a high chance that workers are writing to the same block (meaning they have to wait).

One solution to this is to use a reverse key index.

Another alternative is to create a key that consists of something like :worker_number||nanoTime()||random_number()

ie something that is extremely likely to provide unique numbers. (eg earth being hit by asteroid is more likely than getting a duplicate number)

Answer 8

I don't. I should point out that sometimes people hate what they don't understand.

Sequences are incredibly important for generating unique IDs. Conceptually, it's useful to have a method of generating an ID that does not depend on the contents of a table. You don't need to lock a table in order to generate a unique number.

Sequences can also be useful for generating keys across multiple tables that need to be unique. For instance, if I have a new item entering a system and I want to put a row in several tables at once, I can grab an ID from a sequence are reuse it when I insert into any number of tables. Done properly, I know the ID will not conflict with a value already in the tables and that each row will have the same ID.

I would assume these things are also possible with auto-increment columns as well.