Oracle Sequence
Sequences are special database objects that provide numbers in sequence for input to a table. They are useful for generating primary key values where the input must be unique and in some form of numerical sequence.
Creation of Sequences
Sequences are created by use of the CREATE SEQUENCE command.
Syntax:
create sequence mySeq start with 1 increment by 2 maxvalue 10000 cycle cache 5;
Explanation:
INCREMENT BY- Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending. START WITH- Tells the system which integer to start with.
MINVALUE- Tells the system how low the sequence can go. For ascending sequences, it defaults to 1; for descending sequences, the default value is 10e27-1.
MAXVALUE- Tells the system the highest value that will be allowed. For descending sequences, the default is 1; for ascending sequences, the default is 10e27-1.
CYCLE- Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.
CACHE- Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.
ORDER- Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required. In most cases, the sequences numbers will be in order anyway, so ORDER will not be required. ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.
Accessing NEXTVAL in Sequence
select mySeq.nextval from dual;
Accessing CURRVALin Sequence
select mySeq.CURRVAL from dual;
ALTER Sequence
alter mySeq increment by 14601;
Sequences are special database objects that provide numbers in sequence for input to a table. They are useful for generating primary key values where the input must be unique and in some form of numerical sequence.
Creation of Sequences
Sequences are created by use of the CREATE SEQUENCE command.
Syntax:
create sequence mySeq start with 1 increment by 2 maxvalue 10000 cycle cache 5;
Explanation:
INCREMENT BY- Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending. START WITH- Tells the system which integer to start with.
MINVALUE- Tells the system how low the sequence can go. For ascending sequences, it defaults to 1; for descending sequences, the default value is 10e27-1.
MAXVALUE- Tells the system the highest value that will be allowed. For descending sequences, the default is 1; for ascending sequences, the default is 10e27-1.
CYCLE- Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.
CACHE- Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.
ORDER- Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required. In most cases, the sequences numbers will be in order anyway, so ORDER will not be required. ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.
Accessing NEXTVAL in Sequence
select mySeq.nextval from dual;
Accessing CURRVALin Sequence
select mySeq.CURRVAL from dual;
ALTER Sequence
alter mySeq increment by 14601;
No comments:
Post a Comment