:

Create, Alter and Cache Oracle Sequence

What are Sequences in Oracle?

  • A database object which generates numbers
  • Usually used to generate a value for a primary key column
  • You can select the current and next values from them
  • Are not table specific so can be used to generate a primary key value across multiple tables
  • No locking required if simultaneous generation of key values
  • The last generated value from a user’s session is available only to that user

Note: If your application cannot tolerate gaps in sequences numbers you should not use Oracle sequences as there is no guarantee that gaps won’t occur

Creating a Sequence in Oracle

It’s very easy to create an Oracle sequence:

   CREATE SEQUENCE seq_test
   START WITH 1
   INCREMENT BY 1
   NOMAXVALUE
   NOCYCLE
   CACHE 20;

The statement above creates a sequence called seq_test starting at number 1, incrementing each number by 1, with no maximum value, without cycling back through to the beginning (number 1) when it reaches the maximum value and caches 20 values at a time (more on this later).

Altering Oracle Sequences

In my opinion there is a downside to the way in which Oracle implements its version of sequences and how they are used for the primary key values on tables. The problem is that anyone can increment a sequence independently of the table it is supposed to be used for and also that anyone can insert numbers into the primary key column of a table without the need to use a sequence. It would be nice to be able to link a sequence with a column to auto insert, like is possible in other RDBMS’.

I’ve seen this cause issues in the past because a developer would use an incorrect sequence value for the primary key column of a table. This doesn’t always cause an issue and the problem may not even be seen for many months or even years. What can happen is that someone adds a value which is higher than the current highest value in the sequence. The insert works and everyone is happy, until the sequence reaches that value and cannot insert a row because the primary key will be violated.

In this case it is probable that you will just increment the sequence above the value that was inserted so it can carry on. How do you increment the sequence? It’s really simple:

   ALTER SEQUENCE seq_test INCREMENT BY X;

You would replace the X with a number that you want to increase the sequence by. It really is that easy.

There are many other reasons and ways in which you might want to alter your Oracle sequence, including:

  • Caching more values because you are getting sequence cache contention (more below)
  • Cycling the sequence so that once it reaches its max value it will start from the beginning again
  • Changing the sequence so that there is no max value, or setting one

Caching Sequence Values

Why would you want to cache sequence values? The main reason is if you have a very heavily loaded system with a sequence number for the primary key column you want to reduce contention for inserts as much as possible. One way is to cache more sequence values. This will help you avoid the “enq: SQ – contention” wait event in your database.

How many values should you cache? The default setting for a newly created sequence is 20. We find that to be sufficient for most of our sequences, but the very heavily used ones we increased to 50. If it becomes an issue again we can always increase that cache value further.

Sequence.NEXTVAL

You would usually find the use of the NEXTVAL command when generating a sequence number for inserting into a table. There are a few ways to use it, most notably an efficient and an inefficient way! Let me walk you through both of them, first with the inefficient way:

   -- First we will create a table and sequence
   SQL> create table rj_seq_test (id number(10));

   Table created.

   SQL> create sequence rj_seq;

   Sequence created.

   -- Now this is the inefficient way to do the insert
   SQL> declare
    2  vseqid number;
    3  begin
    4  select rj_seq.nextval into vseqid from dual;
    5  insert into rj_seq_test values (vseqid);
    6  commit;
    7  end;
    8  /

   PL/SQL procedure successfully completed.

There really is no need to have the select statement populating the variable with the next sequence value. It can all be done within the single statement and without the need for the variable at all, as can be seen in the example below:

	    
   SQL> begin
    2  insert into rj_seq_test values (rj_seq.nextval);
    3  end;
    4  /

   PL/SQL procedure successfully completed.

This way is much more efficient, cleaner and clearer so I would recommend sticking with this approach.

Selecting the CURRVAL of an Oracle Sequence

You may want to select the current value of a sequence, in which case you can use the sequence.CURRVAL statement:

   select rj_seq.currval from dual;

It should be noted that this statement will return a different result from one session to another. This is because the sequence value is session specific, so if you selected number 100 from the sequence the currval statement would return 100 until you selected another number from the sequence in the same session. In the meantime, another 100 sequence numbers may have been used in other sessions resulting in the next sequence being 201 from your original session.

Hopefully this has helped clarify any misconceptions you had surrounding working with Oracle sequences.

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *