Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: My one for today: ALTER TABLE foo CACHE;

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

From: Howard J. Rogers <howardjr_at_www.com>
Date: Tue, 30 Jan 2001 21:17:07 +1100
Message-ID: <3a76946e@news.iprimus.com.au>

Perfectly correct, Spencer. And utterly redundant in 8i.

If you've got 8i, anything you might conceivably "CACHE" would be best assigned to a keep buffer pool.

Regards
HJR Spencer <spencerp_at_swbell.net> wrote in message news:hUqd6.206$kq4.158699_at_nnrp2.sbc.net...
> 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 Tue Jan 30 2001 - 04:17:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US