Sequence cache or nocache option

[motto]

Hi,<br><br>&nbsp;&nbsp;&nbsp;Why do we give cache or nocache while we<br>create a sequence.It is compulsory to give<br>these option.could you give some technical<br>information about cache and non cache<br>Tell me what is the default value whether<br>cache or non cache.<br><br>Please explain.<br><br>Thanks in advance<br>Motto

 

[THoey]

From the Oracle 7 Documentation (My comments afterwards):<br><br>CACHE specifies how many values of the sequence Oracle7 pre-allocates and keeps in memory for faster access. This integer value can have 28 or less digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers; thus, the maximum value allowed for CACHE must be less than the value determined by the following formula: <br><br>(CEIL (MAXVALUE-MINVALUE)) / ABS(INCREMENT)<br><br>NOCACHE specifies that values of the sequence are not pre-allocated. <br><br>If you omit both the CACHE parameter and the NOCACHE option, Oracle7 caches 20 sequence numbers by default. However, if you are using Oracle7 with the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option. <br><br>My Comments:&nbsp;&nbsp;It looks like the only purpose to using the CACHE command is that it will preassign a set of sequence numbers to be used next, in hopes of speeding the process up.&nbsp;&nbsp;&nbsp;If NO CACHE is used, when a record is added to a table, a process will be run to find the next sequence number and load it into the field.&nbsp;&nbsp;&nbsp;With the CACHE option, it just takes one of the preassigned values and loads it in.<br><br>If you do not declare CACHE or NO CACHE, it defaults to CACHE and preassigns the next 20 &quot;sequences&quot;.&nbsp;&nbsp;&nbsp;<br><br> <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>

 

[connectxyzzy]

Additionally, if you absolutely need sequential numbers from this sequence, don't cache. It will lose all the number in memory if you have a crash, and you will need to&nbsp;&nbsp;reset the sequence. (You could lose numbers if commits fail, but that is an app issue.)