Wednesday, February 22, 2006

Oracle Sequences

Proactively maintaining your database ... something some people do only AFTER a problem of some kind.

Case in point, a customer recently asked us what sequences are being used as primary keys, what are their maximum values, and are any in danger of running out. Guess what motivated that investigation?

Most of this is rather trivial using only the ALL_SEQUENCES table.

1. What sequences are being used, and do they roll over or run out?

SELECT cycle_flag, sequence_name FROM ALL_SEQUENCES;

2. Which sequences are being used as primary keys?

That's trickier. See, even if you're using a sequence as a primary key, there's no way to tell by just looking at a table somewhere. The INSERT commands could be calling that sequence directly. Edit Or, it could be inserted by a trigger. I'm not aware of any easy, reliable way to determine if sequences are being used as primary keys without looking table by table.

However, generally that is what people like to use sequences for, so there's a good chance that all the sequences are being used as keys somewhere.

3. Which sequences are in danger of running out?

SELECT sequence_name, (max_value - last_number)/increment_by sequences_left
FROM ALL_SEQUENCES
ORDER BY sequences_left;

4. What happens when a sequence runs out?

Well that depends if its a roll-over or not.

CREATE SEQUENCE rollover_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 CYCLE NOCACHE;
CREATE SEQUENCE runout_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 NOCYCLE NOCACHE;

CREATE TABLE sequence_table (roll NUMBER(1), runout NUMBER(1));

Run this three times:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, runout_seq.NEXTVAL);

On the fourth time:
ORA-08004: sequence RUNOUT_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

But the other one rolls over:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, 4);

scott@Robert> SELECT * FROM sequence_table;

ROLL RUNOUT
---------- ----------
1 1
2 2
3 3
2 4


Why is it 2,4 instead of 1,4? Well we "used up" the 1 on the misfire, so now we have a "gap." Every time NEXTVAL is "called", the sequence increments. CURRVAL doesn't. Of course, CURRVAL gives you last_number, which is one that's presumably already in use.

By the way, as usual, you can get some better examples, and a better reference, from Dan Morgan's library.

Those looking for an equivalent to AUTONUMBER in other RDBMS may find that using an Oracle sequence is close to the same functionality. Edit Just use the sequence's NEXTVAL as your insert for your key, or in a trigger, like so:

-- Won't work:
CREATE TABLE MyTable (seq_id NUMBER(1) DEFAULT rollover_seq.NEXTVAL);
ORA-00984: column not allowed here

-- Will work:
CREATE TABLE MyTable (seq_id NUMBER(1));

CREATE OR REPLACE TRIGGER trig_seq BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT rollover_seq.NEXTVAL into :new.seq_id FROM dual;
END;

To prevent this post from getting longer than it needs to be, I'll just send you to AskTom to read a discussion about the performance of using Oracle sequences as primary keys.

One final word, about the NOCACHE command I used up there. The default CACHE value is 20, so I had to use NOCACHE because it would try to CACHE more values than the sequence had. CACHE actually CACHEs the next few values for quicker access. But if ever there is a system failure, those previously-retrieved sequence numbers are gone. You'll be left with a "gap."

Of course, gaps may not be the end of the world for you. If they are, you'll need to use ALTER SEQUENCE to fix them. I've already mentioned two ways to get gaps with sequences, here's one more. If you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started. That'll create a gap, too.

Comments:
"it [a sequence number] could the default value of a column"
Not yet it can't.
create table seqqy (id number default seq.nextval)
*
ERROR at line 1:
ORA-00984: column not allowed here

Another 'gotcha' worth checking is where you use a sequence number to populate a column, and the sequence number is about to hit the limit posed by the column's data size. Yes, your sequence might go up to several gazillion, but if you've defined the column as number(6), then your millionth customer will be your last.

One trick to pick up where sequences are used is to compare the HIGH_VALUE in ALL_TAB_COLUMNS to the LAST_NUMBER in ALL_SEQUENCES. No guarantees of course.
 
Sloppy!!!

Fixed it. Thanks!
 
This is one of those things where terminology is off and the devil is in the details. A sequence can't be a primary key because it's not a field in a table. A sequence is just number generator. A field in a table can use the sequence for population, but inside of Oracle, there is no relation. In your code (your trigger, insert statement) you can link them, but they really are two different things.

Examples: There's not really to stop me from saying,
UPDATE my_table set seq_id = 12 where seq_id = 1;
as long as 12 hasn't been used yet.
or alternatively I can sit at my desk a run
select rollover_seq.nextval from dual;
over and over and burn up values but have no effect on the table.

Just a couple fo more gotchas. Other than that, you are spot on.
 
Jon's got it right:
http://www.lifeaftercoffee.com/2006/02/17/how-to-create-auto-increment-columns-in-oracle/
 
And Howard's got a great summary on doing an "autonumber"
http://dizwell.com/main/content/view/61/83/
 
I'm curious how you would fix gaps with ALTER SEQUENCE.
 
Peter,

I didn't really go into detail with that because its kind of a hack. It's just how I do it. I'll show you using the SAME example as above.

CREATE SEQUENCE rollover_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 CYCLE NOCACHE;
CREATE SEQUENCE runout_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 NOCYCLE NOCACHE;

CREATE TABLE sequence_table (roll NUMBER(1), runout NUMBER(1));

Run this four times, observe the error on the fourth (same as in the article):
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, runout_seq.NEXTVAL);

Note: If you got the gap some other way, you may need to ROLLBACK first, otherwise you'll have to be even more clever to fix it, seeing that you've got a value you'll need to "skip" next time.

Now do this:
ALTER SEQUENCE rollover_seq INCREMENT BY -1;
SELECT rollover_seq.NEXTVAL FROM dual;
ALTER SEQUENCE rollover_seq INCREMENT BY 1;

Now try it:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, 4);

SELECT * FROM sequence_table;
ROLL RUNOUT
---------- ----------
1 1
2 2
3 3
1 4

Cheers,
Robert
 
Thank you for the explanation. I love the irony of using INCREMENT BY -1 (A.K.A. rollback) to fix gaps caused by a ROLLBACK! ;)

But when you said to use ALTER SEQUENCE to fix gapS, two thoughts came to mind-- that Oracle had finally provided an ALTER SEQUENCE name START WITH nnn to reset a sequence to a new starting point (no such luck). Dropping and recreating has negative side effects like lost grants and invalidated dependencies. The other even better thought-- that several non-consecutive gaps that had occurred over time in target keys could be filled-in or "reclaimed" [insert magic here]. Oh well, it was just wishful thinking on my part. Your hack will come in useful for the former without producing the unwanted side effects. Thanks again. :)
 
But what about the performance in such a sequential PK ? The b-tree associated will be so unbalanced because of the inserts of sequential numbers. What do you think about that?
 
Anonymous,

The AskTom link I provided has a discussion on performance.

As for what I think, well I stayed away from that on purpose.

So many people use a primary key that goes 1,2,3... when there is no need for a sequential key.

In most cases, you can use SYS_GUID - that will give you a unique key that is spread out across your disk, reducing the amount of contention on that sequence, and on the part of the disk that houses the most recently added rows.

See a discussion on choosing primary keys here

In the end, it ALL depends on how the table is used. There are all sorts of alternatives and tricks.

My argument against sequential primary keys is the same argument against database independence. Is it a legitimate business need, or not? If not, why bother with it?
 
Lycovian,

Let me turn it around on you. Why do you need autogenerated sequences? How often is it a legitimate business need that all rows have a sequential number assigned to it?

Anyway, you can still have your autonumber, its just a few extra steps (sequence and a trigger).

Oracle tends to be that way. It's more powerful, but also more complex.

Why doesn't it just have autonumber? I don't know. The same reason it doesn't have Boolean I guess.

My advice is to Ask Tom.
 
Is there any query I can run that would tell me which tables have data that was generated from a particular sequence?
 
Anonymous,

No. In the original article, look at my answer to the second question.

A sequence isn't linked to data in a table in any way, even if that's where it originally came from.

The only thing I can think of to determine whether data came from a sequence or not is to audit and log all inserts/updates.

Robert
 
Anonymous: Binary trees get unbalanced, but B-trees don't. There is a common misconception that B-trees are binary trees, but they are quite different.

B-trees have a much higher fan-out, often in the order of 100s, depending on the block size and on the average key size. B-trees with more than 5 levels are rare.

Also, B-trees grow in the opposite direction of binary trees. Whenever a B-tree needs to be expanded, either a sibling or a new root is created, never a new child. When this idea appeared (some 30+ years ago), it was a radical shift of view.

The number of nodes affected by rebalancing a B-tree is much less than for binary trees. This number is always less than twice the height of the B-tree.

I advice naming PK-generating sequences similar to the primary keys, for example: [name]_SEQ and [name]_PK.

This enables easy identification of sequence-primary key pairs.

For legacy systems, you have to go through the trouble of manually identifying these pairs. Once you have done so, I suggest that you create synonyms for the sequences, where the synonyms are named similar to the primary keys. Use the synonyms in any new code as well as a documentation of the legacy.

Regards,
 
From AskTom: (http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:474483191697)

"When Oracle goes to split an index block, there is an optimization (known as the
90/10 split). If we notice that you are always inserting increase values (as
you would with a primary key on a sequence), we do a 90/10 split -- 90% of the
data goes "left", 10% of the data goes right."
 
This is how I determine sequence - table associations. I use it to generate hibernate maping files for oracle with the sequence information.

CREATE OR REPLACE FUNCTION tiggerbodytovarchar (trgname IN VARCHAR2)
RETURN VARCHAR2
IS
tmp VARCHAR2 (4000);
BEGIN
SELECT trigger_body
INTO tmp
FROM user_triggers
WHERE trigger_name = trgname;

RETURN tmp;
END;
/

CREATE MATERIALIZED VIEW table_table_seqname
BUILD IMMEDIATE
AS
SELECT table_name,
UPPER(REGEXP_SUBSTR(REGEXP_SUBSTR( LOWER(tiggerbodytovarchar(trigger_name)), '([[:alpha:]|_])+\.nextval'),'[[:alpha:]|_]+')) AS seq
FROM user_triggers
WHERE
triggering_event = 'INSERT' AND
status = 'ENABLED';
/


CREATE MATERIALIZED VIEW table_sequence
BUILD IMMEDIATE
AS
SELECT table_table_seqname.table_name, seq.* FROM table_table_seqname
INNER JOIN user_sequences seq
ON UPPER(seq.sequence_name) = seq;
/
 
oracle sequences make me sick.
why do they do this anyway.
could they not just create a table with and identity.
I suppose thats why I use MySQL, MS SQL and Sybase.
 
Hello to all,

I have a question i have a table in my Application which is a pk_key column, the sequnce on this are about run out (max value set to 8) and the column legth is also 8 for that particular column.

Now when we tried to alter the column length and then trying to insert the data we are getting
====
ORA-00604: error occurred at recursive SQL level 1 ORA-01438: value larger than specified precision allows for this column.
==

could you please help me how to get rid of this issue.
 
In one of questions above, Peters Nosko says: "Thank you for the explanation. I love the irony of using INCREMENT BY -1 (A.K.A. rollback) to fix gaps caused by a ROLLBACK! ;)",

However, I don't know, in which moment use this solution, to fix the gaps cause bya a Rollback. ¿why?, well, how can I known in which moment a rollback was performed by the user?, this is basically, my dilem :P
 
select ' CREATE or replace SEQUENCE WRITESOURCE_PROD_SUPP.SEQ_NTSITEM_ID MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH ' || S.MAX_VALUE || ' CACHE 100 NOORDER NOCYCLE; ' from
( select max(NETTEXT_STAGE_ITEM_ID) + 1 as MAX_VALUE from WRITESOURCE_PROD_SUPP.nettext_stage_items) S ;
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?