Sequencing in Oracle

Purpose:

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax :

CREATE SEQUENCE sequencename
[INCREMENT BY integer]
[START WITH integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE];

Attributes:

Option	Meaning
START WITH	Specifies the values at which sequence must start. Default is 1.
MAXVALUE	Maximum value the sequence can generate. Default is 10e27-1.
MINVALUE	Minimum value the sequence can generate. Default is 1.
INCREMENT BY	Specifies by how much the value of the sequence is to be incremented. If you want numbers in the descending order give Negative value. Default is 1
CYCLE	Restarts numbers from MINVALUE after reaching MAXVALUE.

Example:

Create Sequence roll_no
start with 100
increment by 1;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you want to drop any Sequence,

"drop sequence rollno;"  (Without quote)

is used.

Difference

Sequence - A sequence is used to generate a number sequence that can be used as a primary key.

Indexes - An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.