To answer the original question, here are some links in the documentation. I
hope they help. I think the documentation expects one to read and understand
it, which takes a lot of time, and help from the experts on the list. If you
already (think you) know it all you might not bother! These quotes are mostly
from the SQL Tuning guide for version 12.1.
Purpose of the Query Optimizer
The optimizer attempts to generate the most optimal execution plan for a SQL
The optimizer choose the plan with the lowest cost among all considered
candidate plans. The optimizer uses available statistics to calculate cost. For
a specific query in a given environment, the cost computation accounts for
factors of query execution such as I/O, CPU, and communication.
For example, a query might request information about employees who are
managers. If the optimizer statistics indicate that 80% of employees are
managers, then the optimizer may decide that a full table scan is most
efficient. However, if statistics indicate that very few employees are
managers, then reading an index followed by a table access by rowid may be more
efficient than a full table scan.
Because the database has many internal statistics and tools at its disposal,
the optimizer is usually in a better position than the user to determine the
optimal method of statement execution. For this reason, all SQL statements use
If the optimizer determines that the query requires most of the blocks in the
table, then it uses a full table scan, even though indexes are available. Full
table scans can use larger I/O calls. Making fewer large I/O calls is cheaper
than making many smaller calls.
Later in the same section:
If a table contains fewer than n blocks under the high water mark, where n
equals the setting for the DB_FILE_MULTIBLOCK_READ_COUNT initialization
parameter, then a full table scan may be cheaper than an index range scan. The
scan may be less expensive regardless of the fraction of tables being accessed
or indexes present.
Guidelines for Writing SQL Statements That Avoid Using Indexes
In some cases, you might want to prevent a SQL statement from using an index
access path. For example, you know that the index is not very selective and a
full table scan would be more efficient.
The 10.2 manual is more direct, from the administration guide.
184.108.40.206 Why a Full Table Scan Is Faster for Accessing Large Amounts of Data
Full table scans are cheaper than index range scans when accessing a large
fraction of the blocks in a table. This is because full table scans can use
larger I/O calls, and making fewer large I/O calls is cheaper than making many
I can't find this text in the 12.1 manuals. It also doesn't discuss the
additional cost of accessing the index which has been pointed out.
I hope this is helpful!