Understanding Access Paths for the Query Optimizer

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i82080

Access paths are ways in which data is retrieved from the database.
In general, index access paths should be used for statements that retrieve a small subset of table rows, while full scans are more efficient when accessing a large portion of the table.
Online transaction processing (OLTP) applications, which consist of short-running SQL statements with high selectivity, often are characterized by the use of index access paths.
Decision support systems, on the other hand, tend to use partitioned tables and perform full scans of the relevant partitions

1 Full Table Scans

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria.
During a full table scan, all blocks in the table that are under the high water mark are scanned.
When Oracle performs a full table scan, the blocks are read sequentially.
Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process.
The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT.
Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

1.1 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 smaller calls.

1.2 When the Optimizer Uses Full Table Scans

The optimizer uses a full table scan in any of the following cases:

1.2.1 Lack of Index

1.2.2 Large Amount of Data

1.2.3 Small Table

1.2.4 High Degree of Parallelism

1.3 Full Table Scan Hints

Use the hint FULL(table alias) to instruct the optimizer to use a full table scan.

The data access paths that can be used to locate and retrieve any row in any table.

·         Full Table Scans

·         Rowid Scans

·         Index Scans

·         Cluster Access

·         Hash Access

·         Sample Table Scans

How the Query Optimizer Chooses an Access Path

The query optimizer chooses an access path based on the following factors:

·         The available access paths for the statement

·         The estimated cost of executing the statement, using each access path or combination of paths

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

·         Optimizer Hints

You can instruct the optimizer to use a specific access path using a hint, except when the statement's FROM clause contains SAMPLE or SAMPLE BLOCK.

·         Old Statistics

For example, if a table has not been analyzed since it was created, and if it has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the high water mark, then the optimizer thinks that the table is small and uses a full table scan. Review the LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table to examine the statistics.