Understanding Access Paths for the Query Optimizer

Access paths are the ways in which data is retrieved from the database. For example whether data will be retrieved through full table scans or index scans. 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. The optimizer take decision which access path it will be used based on the cost of the access path if you don't provide hints.

In the following section I ran 4 query and we see
first one used index unique scan(select a from test_xplan where a=1)
second one used index fast full scan(select a from test_xplan where a>1)
third one used full table scan(select * from test_xplan where a>1) and
fourth one (select * from test_xplan where a=1) used rowid scans.

I used following examples.

SQL> create table test_xplan(a number primary key, b number);
Table created.

SQL> insert into test_xplan select level a , level b from dual connect by level<=100;
100 rows created.
1)SQL> select a from test_xplan where a=1;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2336596025

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_C006438 | 1 | 13 | 1 (0)| 00:00:01 |

2)SQL> select a from test_xplan where a>1;

99 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 623884626

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 1287 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SYS_C006438 | 99 | 1287 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
3)SQL> select * from test_xplan where a>1;

99 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3901816595

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 2574 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_XPLAN | 99 | 2574 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

4)SQL> select * from test_xplan where a=1;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1650693642

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_XPLAN | 1 | 26 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C006438 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

In all cases optimizer decides base on the access path costs which one it will use.

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

1)Full Table Scans-Described in
http://arjudba.blogspot.com/2008/06/what-and-when-oracle-use-full-table.html
2)Rowid Scans-Described in http://arjudba.blogspot.com/2008/06/what-and-when-oracle-uses-rowid-scans.html
3)Index Scans-Described in http://arjudba.blogspot.com/2008/06/what-and-when-index-scans-is-used.html
4)Cluster Access-Described in
5)Hash Access-Described in
6)Sample Table Scans-Described in

Now question is how 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

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 query optimizer is influenced by Optimizer Hints and 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.