Sequence NoCache Significance

It’s kind of usual habit to use sequence values for the primary key of a table. In this approach, what you do is to create a sequence and then access the sequence like mysequence.nextval in the insert statement. In this blog, I’m trying to explain the importance of the CACHE value of the sequence create command.

A sequence can be created using the below 2 commands, one with cache and another without cache.

CREATE SEQUENCE

MY_SEQUENCE_PK

INCREMENT BY 1

START WITH 1

NOCYCLE

NOCACHE

NOORDER;

CREATE SEQUENCE

MY_SEQUENCE_PK

INCREMENT BY 1

START WITH 1

NOCYCLE

CACHE 1000

NOORDER;

How does CACHE work:

When the sequence.nextval is invoked on a sequence created with NOCACHE, an update query fired to set the current value to the sequence in the system tables, apart from the select query. If the sequence is accessed 1000 times, then the update query is also fired 1000 times.

When the sequence.nextval is invoked on a sequence created with CACHE 1000, an update query is fired only once and it will set the current value to a number incremented by 1000. The sequence values will be in the buffer cache and the insert query can use those values. Gain is that we save 1000 update queries while inserting data into the table.

Where this should be used:

The CACHE value can be used, for sure, in data migration cases where large number of records is inserted into the database.

Where this should not be used:

1. You should use the cache pretty much everywhere.
2. If the sequence is seldom used, then cache value need not be provided. For e.g, primary key for a Airplane Model table. New Models doesn’t get added daily and hence the access to this sequence would be very limited.
3. If you need almost continuous values from the sequence and it’s a multi user system, then probably cache should not be used. However, this would cost performance of the statements that use nextval call.