Where is FTS Data Cached?

David Clement

September 2004

Oracle versions 7-8

Full table scans are an exception to the algorithm described in "The Oracle Disk I/O Mechanism." During a full table scan, when the server process reads blocks from data files into buffers in the database buffer cache, it does not arrange the pointers so that these new buffers are at the MRU (most recently used) end. It does that for every other kind of access, but not for a full table scan. Instead, for a full table scan, the buffers are made LRU (least recently used).

The idea is that full table scans usually read a lot of blocks and should be rare. So blocks read in that way should be aged out of the database buffer cache as soon as possible, because they won't be read again soon and they are taking a lot of space.

However, in some cases, the blocks read by a full table scan should be retained. For example, they might represent the contents of a lookup table that is frequently accessed. Oracle provides a storage optimization parameter for such cases. This is the cache clause, used in the create table and alter table statements.

Here is an example of the cache clause in use.

create table davidc.sampletable
tablespace davidc_data
as select *
from user_tables;