Why do you hate sequences on Oracle? [closed]

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,...

Doi

Although this question has some keywords that make it appear argumentative, I believe it is fairly benign. Reading the answers, it does not appear to have generated either subjective or argumentative responses. I'm voting to reopen. – Foreworn

Answer 1

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.

Foreworn

Actually, it's a terrible hassle to create unique IDs across tables with auto-increment columns. For that usecase, sequences are much more convenient. Then again, so is sys_guid(). – Cognize

Answer 2

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

Prism

Autoincrement is limiting. You can't use it more than one table and you can use more than one in one table. For very simple development, they're fine but when you really need flexibility, there's nothing better. – Spirochaetosis

should have said column, can't use more than one for a column. – Spirochaetosis

Sequences rock, love em myself, very useful and powerful. However when all I want is an autoimcrement column, attaching a sequence to a column is a pain, when it could be so much easier. – Prism

Answer 3

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".

Tishtisha

"select max(sequence_id)" queries are usually bugs. Understandable bugs but bugs all the same. If you can insert into a table with any ID using sql*plus then you should never rely on max(sequence_ID). – Lyle

You deserve all the pain you get from Select max(seq) type queries. Should never have made it past code review. – Spirochaetosis

Answer 4

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).

Wisconsin

If autoincrement fields are guaranteed to be "absolute ordering" then that's a HUGE point of serialization and will never scale as efficiently. I hope to God for all the MSSS and MySQL people it's not. – Spirochaetosis

Answer 5

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

Ketone

Harder does not mean 'hard to use'; just not as simple to use. – Ketone

It also means more flexible and more powerful. – Spirochaetosis

@Mark: yes, sequences can do things that SERIAL columns don't. So, IDS has sequences too. But for many people, SERIAL works fine. – Ketone

Answer 6

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.

Fitted

Answer 7

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.

Lyle

Answer 8

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)

Delitescent