Choosing an Access Path

To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.

When choosing an access path, the optimizer is influenced by the following:

• Optimizer hints: The optimizer's choice among available access paths can be overridden with hints. (Using hints requires changing the code)

• Statistics: For example, if a table has not been analyzed since it was created, and the table statistics show that it is small, then the optimizer uses a full table scan. The LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table reflect the statistics used by the optimizer.

• SQL outlines, SQL baseline plans, and SQL profiles: If these exist they will override the optimizer choices. For more information see the SQL Performance Management lesson.

(Oracle Database 11g: Performance Tuning Ch10.17)