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

Let's make life easy for you: don't use the CACHE clause at all. It's a complete waste of time, and does nothing for you.

The standard advice is "use CACHE for small, frequently used tables" -but that was advice knocked up in Oracle 7 when it might have made sense. In 8i, small frequently used tables should be kept in the Keep Buffer Pool, and the use of CACHE is therefore entirely redundant.

You are right that without an index, a select statement, whether qualified with a where clause or not, must do a full table scan. The rule is that blocks retrieved by a full table scan do NOT go up to the MRU end of the LRU list, but are placed around half-way down it (otherwise, a full table scan would tend to flush your entire buffer of useful stuff). The CACHE clause was designed to say that, even though I am using a full table scan, I *want* the blocks at the MRU end of the list, because this is a small frequently used table, and it's important I keep it in the cahce for a long time (and the fact that it is small means it won't be flushing everything else out). As I say, though, that was the best you could do in Oracle 7. You can get much better results in 8 and 8i with the Keep Buffer Pool.

Regards
HJR "VWP914" <vwp914_at_aol.com> wrote in message news:20010929163412.08555.00002153_at_mb-md.aol.com...
> So, is it safe to say that any non-indexed table is subject to a full
table
> scan regardless of what you have for your "where" clause? (I am using
8i).
> The table in question only ever has a handful of rows - non-indexed, yet
is
> accessed by every application that we have. Would that be an application
of
> NOCACHE - so that those rows stay in the Buffer? Or should I just use
KEEP?
> But then KEEP seems to stay till instance shutdown.
>
> -Thomas
Received on Sat Sep 29 2001 - 17:18:24 CDT