What is a Sequence

What is a sequence ?

A sequence is a user created database object that can be shared by mulitple users to generate unique integers.

A typical useage of sequence is to create a primary key value ,which must be unique for each row.

The sequence is generated and incremented by an internal oracle routine.This can be a time saving object because it can reduce the amount of application code needed to write a sequence generating routine.

Sequence numbers are stored and generated independantly of tables.There fore the same sequence can be used for multiple tables.

Syntax:

Create Sequence sequencename

[Increment by n]

[Start with n]

[{Maxvalue n | Nomaxvalue}]

[{Minvalue n | NoMinvalue}]

[{Cycle | NoCycle}]

[{Cache n| Nocache }];

In the above syntax:

Sequencename : is the name of sequence

Increment by n : specifies the interval between the sequence numbers where n is is an integer(If this clause is omitted,the sequence increments by 1)

Start with n : specifie sthe first sequence number to be generated(if this clause is omitted sequence starts with 1)

Maxvalue n : specifies the maxvalue the sequence can generate

Minvalue n : specifies the minimum sequence value

NoMaxvalue:specifies a maximum value of 10^27 for an ascending sequence and -1 for desending sequence(This is default option).

NoMinValue : specifies a minimum value of 1 for ascending sequence and –(10^26) for desending sequence(This is default option)

Cycle | NOCycle :

specifies whether the sequence continues to generate values after reaching its maximum or mimimum value.(NoCycle is default option).

When CYCLE is in effect, then duplicate values can be generated for the sequence.

After an ascending sequence reaches its maximum value it generates its minimum value.

After a descending sequence reaches its minimum value it generates its maximum value.

NoCycle Specifies that values will not be generated for the sequence once the maximum or minimum value for the sequence has been reached. This is the default.

We can create a sequence without using cache as follows becouse by default value of cache is 20.

create sequence e321 increment by 1 maxvalue 40 cycle ;

Cache n|NoCache : specifies how many values oracle server preallocates and keeps in memory(By default oracle server caches 20 values)

CACHE or NO CACHE

Specifies whether to keep some preallocated values in memory for faster access. This is a performance and tuning option.

Specifies the maximum number of sequence values that are preallocated and kept in memory. Preallocating and storing values in the cache reduces synchronous I/O to the log when values are generated for the sequence.

NO CACHE

Specifies that values of the sequence are not to be preallocated. It ensures that there is not a loss of values in the case of a system failure, shutdown or database deactivation. When this option is specified, the values of the sequence are not stored in the cache. In this case, every request for a new value for the sequence results in synchronous I/O to the log.

Example

Create sequence sq1

Increment by 10

Start with 100

Maxvalue 999

NOCache

Nocycle;

Sequence created.

In the abv example we create a sequence sq1 increment by 10 starting value will be 100 then 110 then 120 and so on.This wil continue till 990,since 999 is the mzximum value.

· We can verify our sequence values in the user_sequences data dictinary tables.

Select sequence_name,increment_by,min_value,max_value,last_number from user_sequences;

· Also we can create a sequence for negative values as follows :-

create sequence ac increment by -1 start with 1 maxvalue 5

Sequence created.

The only tricky thing about negative values in sequences is that you may have to explicitly give a MINVALUE or MAXVALUE. Else oracle will give an error.

· Example for creating a sequence using cache and cycle

create sequence k13 increment by 2 maxvalue 6 cycle cache 2;

Here we need to specify the value of cache less than or equal to incremented value.Other wise we get this error : ORA-04013: number to CACHE must be less than one cycle .This is because cache is the memory where oracle stores the value .

· In the below example we increment value of sequence by 3 and maxvalue is 6 .Also memory of cache is 3 .So cache can store only 2 no’s.But we have specified 3.So oracle will give error.

create sequence jsdkj increment by 3 maxvalue 6 cycle cache 3;

create sequence jsdkj increment by 3 maxvalue 6 cycle cache 3

*

ERROR at line 1:

ORA-04013: number to CACHE must be less than one cycle

NextVal and Pseudocolumns :-

· After we create sequence ,it generates sequential numbers for use in our tables.Reference the sequence values by using the nextval and currval pseudocolumns.

· NextVal and Currval Pseudocolumns:-

The nextval pseudocolums is used to extraxt successive sequence numbers from a specified sequence.We must qualify nextval with the sequence name.When we reference sequence.nextval,a new sequence number is generaed and the current sequence number is placed in currval.

The currval pseudocolumns is used to refer to sequence number that the current user has just generated.Nextval must be used to generate a sequence number in the current user’s session before currval can referenced.

When sequence.currval is referenced ,the last value returned to that user’s process is displayed.

Using Sequence :-

Insert into emp values(sq1.nextval,’harsh’);

1 row created.