Re: What is meant by "full table scan?"

"VWP914" <vwp914_at_aol.com> wrote in message news:20010929133532.08467.00002509_at_mb-md.aol.com...
> I often find the phrase "full table scan" used when refering to the
database
> buffer cache. You can specify CACHE on table creation and the blocks (for
full
> table scans) are placed at the MRU end of the list, as opposed to being
put at
> the end. Does a full table scan occur when you query up everything, like
> 'select * from emp'? Does it refer to all columns, or all rows, or both?
>
> I am trying to figure out when I should specify CACHE as opposed to the
> default.
>
> If anyone can shed some light...
>
> Thanks

A full table scan means the table is being read from block 1 to the high water mark of the table. So *all* records are read and -of course- copied to buffer cache. Oracle caches all records you *retrieve* to perform the select, not the records that *satisfy the where clause*. So it doesn't refer to all columns.
A full table scan typically occurs when your select can't use any index present on the table.
As you don't specify your version it is very difficult to tell whether use cache is a good idea. In Oracle 8(i) you can *keep* a certain part of the buffer pool, *recycle* a part of the buffer pool, and accept normal behavior for the rest of the pool.

The full table scan mechanism is of course documented in the performance tuning manual.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Sep 29 2001 - 12:52:23 CDT