Full scans of table bigger than buffer cache.

Dear all,

This question arose a long time ago in this ng when I was a very young DBA and someone kindly answered it but I can't remember what the answer was! If you do a full table scan on a table that's say three times as big as the buffer cache, does Oracle limit the number of blocks that get used for that scan, to leave some for others, or does it just take up all the buffer space it can thereby depressing everybody else's hit rate? What would be the effect of setting the table as "cache"? Is there any point is using enough buffer cache to get the whole table in, o.s. paging permitting? In this particular case the table is about 15% of the data volume. I'm trying to minimise full scans, but they still occur, sometimes a few together.

TIA for any advice / explanation.

Regards,
Terry. Received on Tue Apr 24 2001 - 15:59:53 CDT