Re: Confused: Full table scans and buffer cache

Michal Kuratczyk wrote:
> schonlinner_at_yahoo.com wrote:
> > I read that the blocks of a full table scan will be placed at the LRU
> > end of the buffer cache list. But does this kind of algorithm help in
> > any way in our case when the buffer cache is smaller than a table which
> > is full-table-scanned?
> >
> > In this case I assume that a single full table scan of the large table
> > will throw out everything from the buffer cache, filling it solely with
> > some contents of the large table. Is this correct or am I wrong?
> You are wrong. Think about the LRU algorithm: if it needs a block and there
> are no empty blocks it reuses the oldest one. But since FTS puts blocks at
> this end of list with oldest blocks, it will reuse the blocks it used a
> moment earlier. FTS will just consume some blocks over and over again.
>
> --
> Michal Kuratczyk

Hi Alex,

If your main concern is to avoid the age out of precious blocks from the default buffer cache then maybe it is worthy to consider to setup a recycle buffer cache and, after that, to alter the buffer_pool clause of the ALTER TABLE command for the table which is full scanned, to RECYCLE. In this way your FTS will not affect the data already cached. I'm sure you already know this therefore please take my post just as a short remainder...

Cheers!

alec. Received on Mon Jun 26 2006 - 08:34:31 CDT