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.

Full article...