Explain index range scan and index fast full scan

Explain index range scan and index fast full scan

Created index on column last_name

Index range scan:
—————–

select last_name from cust where last_name=’ACER’;

SQL> set autotrace on;
SQL> select last_name from cust where last_name=’ACER’;
Here is a partial snippet of the output:
——————————————————————————
| Id | Operation        | Name      | Rows | Bytes | Cost (%CPU)| Time     |
——————————————————————————
| 0  | SELECT STATEMENT |           | 15   | 165   | 3 (0)      | 00:00:01 |
|*1  | INDEX RANGE SCAN | CUST_IDX1 | 15   | 165   | 3 (0)      | 00:00:01 |
——————————————————————————

Index fast full scan:
———————

select count(last_name) from cust;

———————————————————————————–
| Id | Operation           | Name      | Rows | Bytes | Cost (%CPU)| Time     |
———————————————————————————–
| 0  | SELECT STATEMENT    |           | 1    | 17    | 170 (0)    | 00:00:01 |
| 1  | SORT AGGREGATE      |           | 1    | 17    |            |          |
| 2  | INDEX FAST FULL SCAN| CUST_IDX1 | 126K | 2093K | 170 (0)    | 00:00:01 |
———————————————————————————–

SQL> select last_name, first_name from cust where last_name = ‘ACER’;

Using SET AUTOTRACE ON and executing the prior query results in the following execution plan:
———————————————————————————————–
| Id | Operation                         | Name    | Rows | Bytes | Cost (%CPU)|  Time    |
———————————————————————————————–
| 0 | SELECT STATEMENT                   |         |  2   | 68    | 1 (0)      | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED|CUST     |  2   | 68    | 1 (0)      | 00:00:01 |
|*2 | INDEX RANGE SCAN                   |CUST_IDX1|  2   |       | 1 (0)      | 00:00:01 |
———————————————————————————————–

It works as INDEX RANGE SCAN identifies the index blocks required to satisfy the results of this query. Additionally the table is read by TABLE ACCESS BY INDEX ROWID BATCHED. The access to the table by the index’s ROWID means that Oracle uses the ROWID (stored in the index) to locate the corresponding rows contained within the table blocks.

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.