Re: Cache a table

"> A little known detail about tablescans in 8.1 (and probably 9.2 but I
> don't recall confirming it) is that the touch count on blocks subject
> to tablescans is NOT increased - so even if a "small" table is loaded
> into the middle of the LRU chain and repeatedly scanned, it will
> always end up falling off the end of the LRU chain as other blocks
> are read into the buffer. It never gets promoted to the hot end unless
> it is also subject to indexed accesses.

If this is correct (Jonathan is seldom wrong!), then it would imply that rows from small table full table scans would be re-read many times as they age-out of the LRU end of the buffer.

This might cause unnecessary disk I/O.

Would this be justification for caching all small tables that experience full table scans in the KEEP pool? Received on Sun Oct 26 2003 - 21:09:54 CST