What and when oracle uses Rowid Scans in execution plans

What is Rowid Scans?
-------------------------------------------
•The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

•To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan of one or more of the table's indexes. Oracle then locates each selected row in the table based on its rowid.

When the Optimizer Uses Rowids?
------------------------------------------------------------
Suppose I made an index on table test_tab column col1. The table has two columns col1 and col2. Now if in my query I use to select both columns like I use SELECT * FROM TEST_TAB WHERE COL1=1; then optimizer at first step retrieve the rowid from index on column col1 and then in the second step optimizer look for row using that rowid.

Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.

An example will make you clear.
SQL> create table test_tab as select level col1, level col2 from dual connect by level<=1000;
Table created.

SQL> set autot trace
SQL> create index test_tab_I on test_tab(col1);
Index created.

Here, I select col1 , the only column by which I created index test_tab_I. So, to select only col1 no need to rowid scan.

SQL> select col1 from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 933728095

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Here I select both col1 and col2. col2 is not inside test_tab_I index. So it used index rowid scan to find the col2 field.
SQL> select * from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement


Note that in both cases dynamic sampling are used. Because I have not gather statistics. If I generate statistics then note will disappear.


SQL> analyze table test_tab estimate statistics;
Table analyzed.

SQL> select * from test_tab where col1=99;


Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)