First page Back Continue Last page Graphics
Full Scans Can Be Useful
Large percentage of data selected
- No WHERE clause
- Predicates not selective
- Conditions not on indexed columns
CBO decides full scan is cheaper
- Multi-block read count is high
- Parallel operations
- Fast hardware
- Cost of multi-block read is low
- See DBMS_STATS.GATHER_SYSTEM_STATS
Notes:
Full table scans sometimes the only access path available and are often the best path. If there is no WHERE clause and / or most of the data in the table will be returned, a full scan is usually the lowest cost execution plan. An exception to this could be if a sort was required and the index could be used to avoid the sort. In this case, CBO could compare the cost of the full scan plus the sort versus the cost of accessing the table via the index.)
The cost of a full scan can be reduced by several factors. Increasing the multi-block read count (see the db_file_multiblock_read_count parameter), enabling parallel processes and fast hardware will all lower the cost of full scans. Use the DBMS_STATS.GATHER_SYSTEM_STATS procedure to educate CBO about the speed of your hardware. This procedure deprecates the OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters. Refer to the Getting to Know CBO lesson for more information on the GATHER_SYSTEM_STATS procedure.