Re: Cache a table

> > 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.

> Certainly an argument in favour. But only if the
> tablescans are very frequent - then you have to
> ensure that the KEEP cache is large enough
> to hold all relevant objects.

Yes, it's important to adjust the KEEP pool to allow 100% caching.

> However, if the database is running on buffered
> file system, then the cost of re-reading from file
> system might not be significant compared to
> other 'real' reads.

Agreed, but in a large system my 4 gig disk caches pales to the 10 gig worth of small tables.

In my experience with the CBO, reading a table with less than 30 blocks via full table scan (db_file_multiblock_read_count=16) is often fastest because of the high values for clustering factor on the index.

Is there a way (i.e. large_table_threshold) to tell Oracle to automatically re-touch these rows? Received on Mon Oct 27 2003 - 06:42:59 CST