Detecting Full-table Scans with EXPLAIN PLAN

As mentioned in prior lessons, unnecessary full-table scans are those scans that search full tables when the information resides within a small percentage of rows, are a common cause of Oracle slow-downs. In a full-table scan, the whole table may not be required to service the query, but Oracle cannot see any indexes that can be used to avoid a full-table scan.
Rather, Oracle reads up to the high water mark for the table. The high water mark indicates the last block in the table that has ever had a row placed on the block. Because of the I/O intensive nature of reading a whole table, Oracle uses multi-block I/O to read the table blocks from disk. This multi-block I/O is governed by the init.ora parameter called db_multiblock_read_count.
The most common cause of an unnecessary full-table scan is the lack of an index. The following SlideShow will help clarify how unnecessary full-table scans occur.

There are cases, however, where full-table scans are appropriate. These cases include:

1. Scans against small tables with fewer than 300 rows
2. Queries that will access more than half of the table rows (These may run faster with a full-table scan than by accessing the rows via the index.)
3. Queries that compute the minimum (MIN), maximum (MAX) or average (AVG) of a table column value, where most of the table rows are required

The goal of detecting full-table scans is to determine if the creation of an index will replace the full-table scan with an index range scan. The most common approach to look for full-table scans is to use a tool such as Oracle’s SQL*Analyze to grab SQL from the library cache, which you practiced using a different scenario in an earlier lesson. Let us try this with the following simulation:

Detecting Full Table Scans using Explain Plan
The next lesson will help you identify nested access paths.