Databases Reference
In-Depth Information
By observation, it is easy to conclude that it consumes fewer resources when using the
sequence instead of the
TRAVELS_COUNTER
table.
Correct definition of a sequence
We have created the
TRAVEL_SEQ
sequence by using the following statement:
CREATE SEQUENCE TRAVEL_SEQ START WITH 10000 INCREMENT BY 1 CACHE 1000;
It's important to highlight the parameter
CACHE
1000
, which points out how many sequential
numbers the database caches.
To keep track of the current sequence number, the database stores it in the
SYS.SEQ$
table.
When we indicate a parameter
CACHE
X
, the database updates the
SYS.SEQ$
and keeps
the current value of the sequence and the high watermark (the last number cached) in the
memory, as we have already called
NEXTVAL
X times on the sequence. When a
NEXTVAL
is
issued on the sequence, the database just increases the counter and if the high watermark
is reached, it caches other X numbers in the same manner.
With the
NOCACHE
parameter instead, for each invocation of the
NEXTVAL
, a sequential
number is generated accessing the
SYS.SEQ$
table, increasing the contention on this object
and decreasing the scalability accordingly.
When you are using the
CACHE
parameter and there are multiple
sessions using that sequence, the numbers won't be contiguous
and after an instance shutdown or when the sequence is aged out
of the shared pool, the cached sequence values are lost.
See also
F
See
Using reverse key indexes
in
Chapter 3
,
Optimizing Storage Structures
for a hint
on using sequential values for primary keys to reduce contention on index leaf blocks
Reducing the number of requests to the
database using materialized views
In this recipe, we will see how to increase the performance of the database—especially in a
data warehousing environment—but the same recipe can be used with small changes in an
OLTP environment as well by using materialized views.
