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.
posted by Wijaya Kusumo at 2/23/2006 12:10:00 PM
14 Comments:
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...
"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.
"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.
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;
MYKEY
----------
10
SQL> select my_seq.currval from dual;
CURRVAL
----------
10
SQL> insert into t values (my_seq.nextval);
1 row created.
SQL> rollback;
Rollback complete.
SQL> select * from t;
MYKEY
----------
10
SQL> select my_seq.currval from dual;
CURRVAL
----------
11
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…
Hello
Do you know if sequence can be used in embedded cobol?
Thanks
David
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.
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: http://hicamel.wordpress.com/2007/04/03/using-model-to-generate-a-sequence-of-numbers/...I just extended it to look for gaps in a sequence controlled primary key.)
Layne
SELECT PK_FIELD
FROM (SELECT LEVEL BADGE_NUM
FROM DUAL
CONNECT BY LEVEL <= (SELECT MAX(PK_FIELD) FROM SEQ_CONTROLLED_TABLE)) MISSING_SEQ
WHERE NOT EXISTS
(SELECT 'TRUE' FROM SEQ_CONTROLLED_TABLE WHERE PK_FIELD = MISSING_SEQ.PK_FIELD)
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)
BEGIN
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;
END
Doesn't this create a new sequence id as opposed to using the old one?
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.
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.
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.
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
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.
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.
Post a Comment
<< Home