What does NOCACHE do?

• Nov 172003

• #2

JtheRipper

IS-IT--Management

Oct 42002

274

GB

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.

 

Upvote 0 Downvote

• Nov 172003

• #3

jimbopalmer

Programmer

Jul 202001

2,904

US

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

For most full table scansyou do NOT want the data keptyou want the current SGA to remain as unchanged as possibleso NOCACHE is the default.

I tried to remain child-likeall I acheived was childish.

 

Upvote 0 Downvote

• Nov 172003

• #4

sem

Programmer

Jun 32000

4,709

UA

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

RegardsDima

 

Upvote 0 Downvote

• Nov 172003

• Thread starter
• #5

Netherbeast

Programmer

Jun 42002

89

US

Thanks for the answers.

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

Thanks

 

Upvote 0 Downvote

• Nov 172003

• #6

avjoshi

IS-IT--Management

May 122003

221

Generally speakinglookup 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.

 

Upvote 0 Downvote

• Nov 172003

• #7

jimbopalmer

Programmer

Jul 202001

2,904

US

Many of the Apps I use have a master table with such parameters as: modules I ownthe language my users speakthe next PO number to assign to a POetc. small tables but frequently accessedand 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 rowsit all fits in one data blockwhen an employee goes over 40 hours I lookup his new pay code)

I tried to remain child-likeall I acheived was childish.

 

Upvote 0 Downvote