In many cases, you should aim to avoid a full table scan by using the best of all possible indexes. Often though, a full table scan cannot be avoided. In these situations, consider some of the following techniques to improve table scan performance:
Reduce the size of the table
The performance of a full table scan is generally proportional to the size of the table to be scanned. There are ways of reducing the size of the table quite substantially, thereby improving full table scan performance. These include:
Use the Parallel Query option
Oracle’s Parallel Query Option is the most effective (and most resource-intensive) way of improving the performance of full table scans. Parallel Query allocates multiple processes to a SQL statement that is based (at least partially) on a full table scan. The table is partitioned into distinct sets of blocks, and each process works on a different set of data. Further processes may be allocated, or the original processes recycled, to perform joins, sorts, and other operations.
The approach of allocating multiple processes to the table scan can reduce execution time dramatically if the hardware and database layout is suitable. In particular, the host computer should have multiple CPUs, or the database should be spread across more than one disk device.
You can enable the Parallel Query option with a PARALLEL hint, or make it the default for a table with the PARALLEL table clause.
Use the Parallel Query option (Oracle 11g Release 2)
A new Parallel Query parameter was introduced in Oracle 11gR2. You can enable Automatic Degree of Parallelism by setting PARALLEL_DEGREE_POLICY to AUTO. This results in the following new behaviours:
Setting PARALLEL_DEGREE_POLICY to MANUAL results in Oracle 11gR1 behaviour.
There are two new wait events in Oracle 11gR2 to help identify when a statement has been queued due to the parallel slaves being busy.
Use the CACHE hint
Normally, rows retrieved by most full table scans are flushed almost immediately from Oracle’s cache. This is sensible; otherwise, full table scans could saturate the cache completely, and push out rows retrieved from index retrievals. However, this does mean that subsequent table scans of the same table are unlikely to find a match in the cache, and therefore incur a high physical I/O rate.
You can encourage Oracle to keep these rows within the cache by using the CACHE hint or the CACHE table setting. Oracle then places the rows retrieved at the Least Recently Used end of the LRU chain, and they persist in the cache for a much longer period of time.
If the number of rows you want to retrieve from a table is greater than an index lookup could retrieve effectively, but still only a fraction of the table itself (say between 10% and 40% of total), you could consider partitioning the table.
For instance, suppose that a SALES table contains all sales records for the past 4 years and that you need to scan all sales records for the current financial year frequently in order to calculate year-to-date totals. The proportion or rows scanned is far greater than an index lookup would support comfortably, but is still only a fraction of the total table.
If you partition the table by financial year, you can restrict processing to only those records that match the appropriate financial year. Potentially, this could reduce scan time by 75% or more.
Use The Fast Full Index Scan
If a query needs to access all or most of the rows in a table, but only a subset of the columns, you can consider using a fast full index scan to retrieve the rows. To do this, you need to create an index that contains all the columns included in the SELECT and WHERE clauses of the query. If these columns comprise only a small subset of the entire table, the index is substantially smaller. Oracle is able to scan the index more quickly than it could scan the table. There is an overhead involved in maintaining the index that affects the performance of INSERT, UPDATE, and DELETE statements.
Using an index to perform the equivalent of a full table scan is possible in earlier versions of Oracle, but only since Oracle 7.3 is the index scan able to use multi-block read and parallel query capabilities.