Job Seekers   Employers
dbapool: Authors | Submissions | Contact Us
   Forgot password? | Sign up
  Home   Discussion Forum   Articles   Interview Questions   FAQs   Scripts   Rewards   Analyzer   White Papers   Blog   Certification   Downloads   Tools
   
Sequence in Oracle




By Gayathri Sathiamoorthy
Jan 30, 2007

Digg! digg!     Print    email to friend Email to Friend

Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.




Sequence in Oracle

What is a sequence?

There are many instances where we want a continuous seqnence of numbers to be generated. For example, lets say that we develop a POS (Point of Sale) system (an application used to bill the items in a store). In this system, the bill number would be a continuous sequence of numbers. Oracle's sequences allow us to generate these continuous numbers.

Why sequence?

In the real time, there would be more than one counter where billing could be done and all of these users might want a bill number at the same time. If we were to assign the number manually, there is a high possibility that these numbers are replicated, which might not be correct. When we use sequence numbers, oracle takes care generating non-recurring numbers and also takes care of the locking.

Effect on Performance

If we were not to use a sequence to generate the bill numbers, the most common way would be to find the latest bill number and increment it by 1 use it for the transaction. In an attempt to find the latest bill number, one would use the max function in the query which would tend to reduce the performance of the query especially when there are more than a few hundreds of thousands records in the table.

Next, there would be a high chance of duplicating a bill number especially if 2 users are trying to get the bill number at the same time. So sequence gives us an option to improve performance and also avoid duplication. Oracle internally takes care of the locking mechanism when generating the next sequence number.

Syntax:

CREATE SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
];

Description of the syntax

INCREMENT BY

Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

START WITH

Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.

MAXVALUE

Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.

NOMAXVALUE

Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.

MINVALUE

Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.

NOMINVALUE

Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.

CYCLE

Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE

Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

CACHE

Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values. For example, if you insert 10 records with the NEXTVAL and then issue a ROLLBACK command, the sequence does not get rolled back. Rather, the 10 records will have incremented the sequence and the next insert will have the 11th value of the sequence. This will result in "holes" in the sequence. Oracle chose this so that multiple people can safely use a sequence without fear of duplicate values.

To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value (provided one can ensure that no other user is going to get the nextval of the sequence at that point of time.)

NOCACHE

Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

ORDER

Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

NOORDER

Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

EXAMPLE:

CREATE SEQUENCE Seq_BillNumber
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

CURRENTVAL AND NEXTVAL

The two operators for sequences are CURRENTVAL and NEXTVAL, that obviously gives the current value and the next value of the sequence.

They are used as follows

Sequencename.CURRENTVAL and Sequencename.NEXTVAL.
Eg. Select seqname.NEXTVAL from dual;

When NEXTVAL operator is used, the following takes place

1. It fetches the CURRENTVAL
2. Increments the value in CURRENTVAL by the increment specified in the sequence.
3. Updates the CURRENTVAL to the newly obtained value and returns the new value.

To access a sequence for the first time, sequence.NEXTVAL must be referred before you can refer to sequence.CURRVAL. Every sequence.NEXTVAL expression increments the sequence, regardless of whether the transaction is subsequently committed or rolled back.

RESTRICTIONS

The following restrictions apply to these operators in SQL statements:

1. You must have Select privilege on the sequence.
2. In a CREATE TABLE or ALTER TABLE statement, you cannot specify NEXTVAL or CURRVAL in the following contexts:
        - In the Default clause
        - In the definition of a check constraint.
3. In a SELECT statement, you cannot specify NEXTVAL or CURRVAL in the following contexts:
        - In the projection list when the DISTINCT keyword is used
        - In the WHERE, GROUP BY, or ORDER BY clauses
        - In a subquery
        - When the UNION operator combines SELECT statements.
4.You also cannot specify NEXTVAL or CURRVAL in these contexts:
        - In fragmentation expressions
        - Views
        - In reference to a remote sequence object in another database.

 

 

 
About author:

Gayathri is an Oracle certified DBA with over 6 yrs of experience. She has expertise in Oracle Database Administration, Query optimization, PL/SQL Programming, Transaction Management Mechanism, Referential Integrity, Stored Procedures, Triggers, Auditing, Locking & Concurrency and Export/Import utilities.

 




  About Us Advertise Terms of Use Privacy Newsletters Contact Us    

Home   Discussion Forum   FAQs  Articles  Jobs   Newsletters  Directory  Downloads 

Our Premium Sponsor