Introduction To Oracle Sequences

Owned by Clive Otten (Unlicensed)
Jan 20, 2020
4 min read

In the world of database replication, sequences seem to be a recurring theme.  In fact, I don’t think that a week goes by without a prospective customer asking a question or two about sequences and what happens in regards to replication.  To that end I thought it might be a good idea to talk to replication.  Before we dig into replication and sequences I think it would be a good idea that we get the basics of Oracle sequence generation down first as there seems to be some needed clarification around them.

Sequences are actually simple objects in an Oracle schema.  Yes, the very first thing that you should know is that a sequence is a schema object.  The next thing you should know is that sequences have one purpose:  to auto generate numbers.  Typically they are used for the creation of primary keys although they don’t have to be. Let’s look at sample create sequence DDL and then discuss the options:

CREATE SEQUENCE seq_employee_id START WITH 12 INCREMENT BY 2 MAXVALUE 3000 NOCYCLE CACHE 20;

We start with giving the sequence a name.  Here we named it seq_employee_id.  It is very important to note that a sequence is NOT tied to any table or any other object in the schema.  However giving it a name that makes sense is always important.  Here it is created with a name that will probably be used to create employee ids. Next we have START WITH.  You can certainly start with any integer that you choose.  Here I have decided to start with the number 12. INCREMENT BY is of course what the number is incremented by.  The first number will be 12 and the next will be 14 and then 16.  If you had not used this option the default of 1 would have been used.  You can have the increment by any integer that you choose.  The INCREMENT BY option will be important when we discuss how this comes into play with replication.

Sequences can have maximum values.  Here we see that I put 3000 as my maximum value.  By default the maximum value is 1 octillion.  What is an octillion you ask?  1027  Yes a really big number.  You may want to think through the reason if you plan on putting a smaller maximum value.  Optionally,  you can also have a minimum value. NOCYCLE means that the sequence will not cycle when it reaches the maximum value.  If we had CYCLE when the sequence reached 3000 (our maximum) the sequence would have started over again at the minimum value of the sequence. (Which as we did not specify the value would have been 1, the default)  Be very careful about using the CYCLE option if you plan on using the sequence to generate primary keys.  Notice in the first paragraph I mentioned that sequences are automatic number generators?  It does not say that they are UNIQUE generators.  They can be unique just make sure to choose the correct option.  Fortunately, NOCYCLE is the default. CACHE 20 means that Oracle will predetermine the next 20 values of the given sequence.  It will hold those in memory so that they will not have to be calculated in the future.  The default is 20.  The lowest value is 2.  CACHE is recommended if you use the sequence a lot or if using RAC.  Remember this parameter as we will talk about how this is used in regards to replication. Now let’s use this sequence.  The sequence would typically be used to insert employee values when inserting a new employee into the employees table.

INSERT INTO employees VALUES (seq_employees_id.NEXTVAL, etc etc );

NEXTVAL might be a new term for some. In Oracle sequences, NEXTVAL is the 'next value' in the sequence. NEXTVAL is what is known as a pseudocolumn.  In our example, the first NEXTVAL will be 12.  When another employee is inserted the Oracle sequence NEXTVAL will give us the value of 14. Let’s look at this in terms of not using the employee table for a moment. You can do the following:

SELECT seq_employee_id.NEXTVAL FROM dual;

This would give you the next value in the sequence. The very act of looking at the NEXTVAL uses the number… so every time you run this command you would use the number.  To see the current number you would want to do this:

The pseudocolumn CURRVAL shows the current value of the sequence.  You can run the above command multiple times and it will always show you the current value.  It will NOT ‘use’ the number like NEXTVAL will. Let’s look at another example.

Let’s say we just created the sequence from earlier and then inserted some rows.  This example would have inserted 12 in for TEST, 14 for TEST2.  But then a rollback occurs.  What value gets placed in TEST3? The value put in TEST3 would NOT be 12.  It would be 16.  When you invoke the sequence the number gets used up.  There is no guarantee that numbers will be in order.  Rollbacks and other users might be using that sequence which can cause sequences to be ‘out of order’.  But remember this ‘out of order’ might just be that someone else used those numbers. Now we have a base  of understanding about Oracle sequences. Next week we will talk about sequences and the relationship with database replication. We will talk about some of the misconceptions regarding sequences and also some of the pitfalls and things that you nee to watch out for.