You dont need that sequence number

November 16, 2015

1 minute

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


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

So say it with pride … “I DONT WANT TO KNOW WHAT MY SEQUENCE VALUES ARE!!!” 🙂

7 responses to “You dont need that sequence number”

Sai Gautam

November 17, 2015 at 1:02 am

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.

Reply
connormcdonald

November 17, 2015 at 11:49 am

Thanks for dropping by and taking time to comment.

Wouldnt

insert into T values ( nvl(:var, seq.nextval ) )
returning s into :var;

do the trick ?

Reply
1. Boneist
  
  November 17, 2015 at 5:12 pm
  
  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!
  
  Reply
  1. Arian
    
    November 18, 2015 at 3:37 am
    
    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.
    
    Regards,
    
    Arian
    
    Reply
Farzad Soltani (@FarzadSo)

February 3, 2018 at 7:33 pm

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.

Reply
connormcdonald

February 3, 2018 at 7:49 pm

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.

Reply
Arian

February 4, 2018 at 5:25 am

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.

Reply