|
Proper Use of
Oracle Sequences
BEGIN
select
INSTANCE_NUMBER INTO INST_ID
FROM
V$INSTANCE;
select
EMP_ID_SEQ.NEXTVAL INTO SEQ_NUM
FROM
DUAL;
INST_SEQ_ID:=(INST_ID-1)*100000 + SEQ_NUM;
:NEW.EMP_ID:=INST_SEQ_ID;
END;.
Generally, sequences can be cached with
cache values as high as 200 in RAC. This is
much higher than for a regular Oracle
instance. If there is insufficient caching,
then contention can result which will show
up as an increase in service times. If there
are performance problems due to sequences,
then examine the row cache locks statistics
in the v$system_eventview to determine
whether the problem is due to the use of
Oracle sequences.
Use Oracle sequences to generate unique
numbers and set the CACHE parameter to high
value, if needed.
Administration
of Sequences
Sequences are special database objects that
provide numbers in sequence for input to a
table. They are useful for providing
generated primary key values and for input
of number type columns such as purchase
order, employee number, sample number, and
sales order number, 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.
where:
sequence_name. The name you want the
sequence to have. This may include the user
name if created from an account with DBA
privilege.
n. An integer, positive or negative.
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. See
Altering Oracle sequences
See my notes on Oracle sequences here:
|
and include the URL for the page.
