Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




How the Optimizer Chooses a Full-Table Scan
Oracle Tips by Burleson

There are several factors that influence the behavior of Oracle SQL with respect to invoking a full-table scan. These factors center around the choice of optimizer_mode and the state of dictionary statistics when using the CBO.

Rule-based Optimizer Mode

The rule-based Oracle optimizer is less sophisticated than the cost-based optimizer and does not have any information about the internal characteristics of the data inside the tables specified in the query. Hence, the RBO will always use an index if it detects that an index is available.

The Cost-Based Optimizer Modes

The cost-based optimizer modes, FIRST_ROWS and ALL_ROWS, have information about the data inside the tables. When the CBO estimates that the cost of a full-table scan is less than that of an index access, it may sometimes choose to perform a full-table scan even if indexes are present. The CBO uses the following factors to determine whether to perform a full-table scan:

  • The db_block_size   Larger blocks will allow for the access of more rows in a single disk I/O.

  • Multiblock I/O   The number of blocks that can be read in a multiblock I/O depends on the setting of the db_file_multiblock_read_count initialization parameter.

  • Index statistics   The CBO evaluates the selectivity of the index and the depth of the index tree. It then computes the number of I/O operations that are required to service the query via the index.

  • The high-water mark for the table   The high-water mark for the table determines the amount of data that is stored in the table. In cases where a significant number of rows have been deleted from a table, the high-water mark remains at the original level, and a full-table scan will often read many “dead” blocks below the high-water mark.

While in Oracle7 and Oracle8 the CBO would often falsely favor a full-table scan, in Oracle8i the CBO has been improved to the point that it will make an intelligent choice. I must also note that there are differences in the CBO optimizer modes and the propensity to invoke a full-table scan.

ALL_ROWS Optimizer Mode

This is a cost-based optimizer mode that ensures that the overall query time is minimized, even if it takes longer to receive the first row. This usually involves choosing a parallel full-table scan over a full-index scan. Because the ALL_ROWS mode favors full-table scans, the ALL_ROWS mode is best suited for batch-oriented queries where intermediate rows are not required for viewing

FIRST_ROWS Optimizer Mode

The FIRST_ROWS optimizer mode is designed to return rows as quickly as possible and will only perform a full-table scan when no indexes are available to service access to the table.

When the CBO Chooses a False Full-Table Scan

As I noted, the CBO will sometimes make a false choice in favor of a full-table scan, especially in Oracle7 and Oracle8. This problem occurs when the following conditions are true:

  •  High-water mark too high When a significant number of deletes have taken place within a table, the high-water mark may be far higher than the actual number of populated blocks. Hence, the CBO will often wrongly invoke a full-table scan, relying on the high-water mark.

  • Wrong optimizer_modeIf the optimizer_mode is set to ALL_ROWS or CHOOSE, the SQL optimizer may favor full-table scans. If you want fast OLTP optimization, be sure to set your optimizer_mode to FIRST_ROWS.

  • Poor statistics If tables have grown significantly and the tables have not been reanalyzed to repopulate statistics, then you may get false full-table scans because the CBO thinks the tables are still small.

  • Skewed indexes   If a candidate index in a query has skewed values, then the CBO might wrongly choose a full-table scan. For example, consider a query that asks for rows where region=southern. You have an index on the region column, but only one percent of the entries are for the southern region. In the absence of column histograms, the CBO does not know that southern region has high selectivity, and so it chooses a full-table scan.

Now that you see why the CBO may make a poor decision, let’s explore methods for ensuring that we do not have unwanted full-table scans.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter