What does NOCACHE do? (2025)

Im new at this and I dont understand what NOCACHE does. Can anyone provide me with an answer?

thanks in advance

The CACHE option specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a FULL table scan is performed.

The NOCACHE option specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a FULL table scan is performed. This will cause the blocks read with a full table scan to be immediately flushed from the buffer cache.

You would normally CACHE a table when it was VERY small, one or two data buffers but frequently used, lookup tables may fit this. In this scenario you may find that unindexed full table scan from the SGA is faster than an index, so you want the table to stay in the SGA.

For most full table scans, you do NOT want the data kept, you want the current SGA to remain as unchanged as possible, so NOCACHE is the default.

I tried to remain child-like, all I acheived was childish.

You may also use this keyword when creating sequences. This means that no values are buffered in memory for future requests.

Regards, Dima

Thanks for the answers.

Jimbo, you mentioned a "look up table." What do you mean by that? Is that a faster way of doing SELECT * from table_name?

Thanks

Generally speaking, lookup tables are reference tables. They are small in size and are predominently used to do cade lookups. Because they are small in size it's better to cash them.

Hope this helps.

Anand.

Many of the Apps I use have a master table with such parameters as: modules I own, the language my users speak, the next PO number to assign to a PO, etc. small tables but frequently accessed, and often very static. Forcing them to stay in the SGA can speed up routine actions (every form may check the user's language)

You may also have lookup tables that remember Oracle anonoumus Keys

this regular code becomes that overtime code
this temporary Foreman code become that overtime temporary foreman code
(my table like this only has 12 rows, it all fits in one data block, when an employee goes over 40 hours I lookup his new pay code)

I tried to remain child-like, all I acheived was childish.