Thursday, February 23, 2006

Potential pitfall with Oracle Sequence

Robert Vollman talks about Oracle sequences in his blog. Basically there are 3 potential issues with Oracle sequences that we need to consider when doing db design:
1. The squence number may "jump", that is 1,2,3,5,6,8,... with number 4 and 7 missing. One reason is if you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started.
2. By default, the sequence caches the 20 next values for faster retrieval. So if there is a system failure, we will lost the cached values upon system recovery. Thus you end up with a gap of upto 20. One way to avoid this is to use NOCACHE command when creating the sequence. And of course you lose the benefit cache brings as well.
3. Gary Myers also pointed that the sequence number can only grow upto the column's data size. For example, if we have column as number(6), then the max value will be 999999. 


Anonymous Anonymous said...

AFAIK sequences and columns are totally unrelated. A sequence number can be larger than any column size you can have in your tables. A sequence is just a number generator, nothing more. It has no knowldege of tables and columns. Nothing in the data dictionary associates a sequence with a column. Of course, you can't fit a number too large in a column too small - remember NUMBER in Oracle has 38 significant digits...

9/21/2006 9:57 PM  
Anonymous Anonymous said...

"potential issues". They aren't "issues". They are "design decisions".

1) "if you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started". How could it rollback? Other transaction may have used following numbers. It should keep a list of "unused" numebers. Anyway a sequence *is not* designed to generate a list of numbers without "holes". It is designed to generate unique increasing numbers.

2) The cache is stored in memory to avoid to update the table that stores sequence number every time a number is used. NOCACHE will force an update every time NEXTVAL is called. Could slow down a system really.

Again, see 1). Sequences are not designed to hand out list without holes.

9/21/2006 10:04 PM  
Blogger Wijaya Kusumo said...

"Of course, you can't fit a number too large in a column too small - remember NUMBER in Oracle has 38 significant digits..."

This is exactly what I meant by the potential pitfall. Very often the generated seq number is stored in a column, and that is the gotcha in this case.

9/22/2006 10:53 AM  
Blogger Wijaya Kusumo said...

We could roll back a transaction that spans multiple DML's. But if one of the DML issues a my_seq.nextval statement, and we rollback the entire transaction, then there will be “hole” in the sequence numbering.

Here is a quick test:

SQL> create table t (mykey number);

Table created.

SQL> create sequence my_seq
2 increment by 1
3 start with 10
4 nocycle;

Sequence created.

SQL> insert into t values (my_seq.nextval);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;


SQL> select my_seq.currval from dual;


SQL> insert into t values (my_seq.nextval);

1 row created.

SQL> rollback;

Rollback complete.

SQL> select * from t;


SQL> select my_seq.currval from dual;


As you can see, it is the intention of this blog entry to emphasis: “Sequences are not designed to hand out list without holes.”

The hole is fine for most of us. But there could be business constraint that mandates the “no-hole” situations, for example financial transaction for auditing purpose. How to address this is not the intention of this article. There are many discussions about this, for example this asktom article. Why there is the business rule in the first place? Dilbert may have the answer…

9/22/2006 11:23 AM  
Anonymous Anonymous said...


Do you know if sequence can be used in embedded cobol?


11/28/2006 12:42 AM  
Blogger Wijaya Kusumo said...

I don't know much about COBOL. But as long as you can connect to the database and execute "select my_seq.currval from dual", then it should work.

11/28/2006 9:40 AM  
Anonymous Layne Robinson said...

If you use a sequence to control primary keys, you can always find the gaps using the query below. (For full disclosure, the connect by query came from this blog: just extended it to look for gaps in a sequence controlled primary key.)



4/27/2007 10:10 PM  
Anonymous Anonymous said...

Can someone just make this perfectly clear, I think that I am correct in saying this, but no one actually says this, it's just implied:

It is not possible to insert a value in a column that uses a automatic sequence.

For example if I called a stored procedure that takes a parameter and the procedure deletes all of the data in a table for that parameter and tries to reinsert using that same parameter, the actual value inserted in the database will be the next automatic sequence (right?)

update (id_column, array_of_numbers)


delete from table where id=id_column;

for i in 1..array_of_numbers.count loop
insert into table values (id_columnm, array_of_numbers(i));
end loop;


Doesn't this create a new sequence id as opposed to using the old one?

8/30/2007 6:46 PM  
Anonymous Anonymous said...

There is no "automatic sequence" in Oracle actually, you still have to call it during the insert. What Oracle provides is a way to generate the next value of a sequence.

For instance:

insert into table values (my_seq.nextval, array_of_numbers(i));

This creates a new sequence id as opposed to using the old one.
Of course we could also insert any number we like:

insert into table values (123, array_of_numbers(i));

What we're talking here is that my_seq.nextval does not guarantee sequential values, since there could be gaps.

8/31/2007 2:37 PM  
Blogger Asif Momen said...

Creating sequences with NOCACHE will have performance impact. If a sequence is occasionally used then this impact is negligible but a heavy use of a sequence will surely top in STATSPACK/AWR report as in my case.

9/11/2007 3:07 PM  
Anonymous Odi said...

What's a lot more interesting is WHERE the sequence is cached. It is apparently cached in the SGA (and not per connection). That gives us a SINGLE cache, and not multiple ones. This means that connections concurrently obtaining values from the sequence will see numbers that are consistent with the order in which they are obtained: connection A will always see a smaller number than connection B, if A queries before B.

4/14/2008 5:32 PM  
Anonymous Anonymous said...

The above comment (Odi) is incorrect for distributed Oracle environment (RAC). To make your code portable, don't assume a sequence is increasing when cache is used. What it can always provide is just an unique number

6/24/2008 11:03 AM  
Anonymous Anonymous said...

I am facing an werid issue where the sequence is jumping to and forth.

Eg: Sequence as on
1st Jan is 2001
2nd Jan it is 1001
3rd Jan it is 2004
4th Jan it is 501 etc...

How do i need to handle this issue i am facing this issue in production hence any inputs will help me a lot.

9/24/2009 8:27 PM  
Anonymous Anonymous said...

you have a nice site. thanks for sharing this valuable resources. keep it up. anyway, various kinds of ebooks are available here

12/16/2009 11:44 AM  
Anonymous oracle ebs said...

Nice post. Your post always make me learn something new that I have not faced yet. I will use this command to avoid this problem. Also Gray has pointed the true fact and sequence number can grow up to the data size allotted.

6/23/2011 4:21 PM  

Post a Comment

<< Home