You dont need that sequence number

I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code:

SELECT my_sequence.nextval INTO :some_variable FROM dual;
INSERT INTO my_table VALUES (:some_variable, ....);

My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before you inserted it ? It is simply, easier, and more efficient just to get it back from the insert statement itself.

INSERT INTO MY_TABLE (seq_col, ...)
VALUES (my_sequence.nextval, ...)
RETURNING seq_col INTO :some_variable;

And with 12c, sequences can now be nominated as part of the DEFAULT value for a column, so you don’t need to refer to it at all…and you STILL can have the number

VALUES ( ...)
RETURNING seq_col INTO :some_variable;


7 thoughts on “You dont need that sequence number

  1. How about the situation where I need the same sequence number for a single insert statement? I would need to put it into a variable so that I don’t have a different sequence for each row as compared to a bunch or rows.

    • That assumes you’re doing a single row insert, but if you’ve multiple rows that are being inserted at the same time, all with the same sequence number, then I could see that needing to store the seq val in a variable once, and then use the variable to do the insert is a valid case. But that’s a bit different from the case you were talking about in your post, I think!

      • I think Sai is talking about repeating the same value within the same row. In that case a .currval will do nicely.
        INSERT INTO MY_TABLE (seq_col, next_col)
        VALUES (my_sequence.nextval, my_sequence.currval)

        Boneist might be talking about the insert all statement? That works with the currval too.

        Please correct me if I’m wrong.



  2. Connor, Can you please give me a solution for generating a sequence that actually works?
    For when using nextval i’ve noticed sometimes it produces these numbers : 1, 2, 21, 22, 23, 4, …

    So it’s quite random.

    What’s the best way to have 1, 2, 3, 4, 5, … as the id of a table and when for instance number 3 is deleted, the next number to insert will be 3.

  3. Its not random …. Its exactly as its meant to be,

    Sequence numbers are cached (for performance) and the default 20, so you get 1,2, 3, 4…and then if its aged out of memory, the next one will be 21 (because numbers up to 20 were already cached). If you have a low volume of inserts, you could set the sequence to NOCACHE, but don’t forget, you will STILL get gaps, because if a transaction fails, then that sequence value is lost forever.

    If you really want contiguous IDs, then its potentially a huge scalability inhibitor…Using your example, when I delete seq #3, then if you *really* want the next person to get that #3, then you need to wait until I commit or rollback. *Everyone* has to wait.

    If you are prepared to *not* wait, ie, the next person gets (say) 12, 13, 14….and then you want someone much later on to “come back” and grab #3, then you aren’t really much different to what a sequence offers now, ie, the possibility of gaps.

  4. Also don’t forget that there are very few real business cases for contiguous sequences. And most of those business cases will not allow deletion of a number.
    What would be the use of having a contiguous sequence when you can delete the numbers and fill them in on the next insert? The only reason I can think of is a reporting requirement. But then you’d be better of with a view that shows a rowcount.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.