When is a Full Table Scan a Good Thing?
Oracle versions 7-8
No row-source operation is good or bad in itself. Each is the best choice in some contexts. A full-table scan (FTS) is faster than index access in the following situations. At the same time, some of these situations can be avoided.
If reading right through the table would be less effort than retrieving rows by probing an index, then FTS is actually the better choice. This is usually the case when the table is small, that is, 2 percent or less of the size of the database block buffer. The size is in v$sga if you need to check it. In such a case, Oracle can probably read the table in one multiblock scan.
The advantage or disadvantage of FTS is less evident if the table is of small to medium size. Many DBAs use the rule of thumb that if a SQL statement affects 20 percent of the table data or more, it would take as long or longer to pick up row IDs from index as it would to scan the table. This can be misleading inasmuch as the real question is not one of row access but of block access. A hypothetical table with 200 short rows stored on average in each of 5 4K blocks might take exactly as long to return one row as it would to return 100, in the case where all 100 needed happened to be in one block (not an unrealistic possibility if you are querying a historical table by timestamp). Thus, data distribution can have a crucial effect. Another widely cited rule of thumb is that in the unusual case where the table data is perfectly even distributed (for example, as the result of an insensitive load-balancing algorithm) to read 5 percent of the table data is enough to make FTS just as fast as index access to the same rows.
In other cases, FTS is enforced by circumstances that could be
avoided. If any operation is performed on a column in the
In the case of a composite index, if the leading edge of the index is
omitted from the