Re: My one for today: ALTER TABLE foo CACHE;

a little more precisely, CACHE only affects placement of blocks in the buffer pool when a table is accessed by a full table scan.

for a table defined as NOCACHE (which is the default), when a full table scan is performed, blocks accessed from disk are placed into the buffer pool at the "least recently used" LRU end of the chain.

for a table defined as CACHE, on the other hand, a full table scan will place blocks accessed from disk into the buffer pool at the "most recently used" MRU end of the chain, which is where all index blocks and data blocks accessed through an index or by ROWID are placed.

use the CACHE keyword with caution. usually, CACHE is specified only for "small" tables that are frequently accessed by full table scans. a full table scan on a "very large" table that has the CACHE keyword specified, will force oracle to flush a large number of recently used blocks from the buffer pool to make room for the "more recently used" data blocks. if the flushed blocks are needed again (which is likely) the blocks will have to be read from disk again, and this can have a significant (negative) impact on database performance.

"Michael Bialik" <michael_bialik_at_my-deja.com> wrote in message news:954l9s$fde$1_at_nnrp1.deja.com...
> Hi.
>
> As I understand it - the accessed block from FOO table are placed at
> beginning of LRU list. That's the only difference.
>
> HTH. Michael.
>
> In article <954k9g$ehb$1_at_nnrp1.deja.com>,
> fooguy <jweisen_at_my-deja.com> wrote:
> > Here's my stupid question for today.
> >
> > When I do a ALTER TABLE foo CACHE; what exactly happens? How does that
> > affect the table in regards to read speed, write speed, what happens at
> > shutdown, etc?
> >
> > Thanks,
> > John
> >
> > --
> > *********************************************
> > "All I every wanted from life was to see
> > Larry Wall give Bill Gates a Perl Necklace."
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Received on Mon Jan 29 2001 - 21:52:51 CST