What are Rowid Scans ?

I came across this at one of the blog-posts (seems now removed). Reference to blog mentioned at end-of-blog post. Thought it was still worth mentioning here.


  • 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)

reference : http://arjudba.blogspot.sg/2008/06/what-and-when-oracle-uses-rowid-scans.html

Advertisements

One comment

  1. Pingback: What are RowID Scans | Links to Various sources

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s