Explain index range scan and index fast full scan

Explain index range scan and index fast full scan

Example will explain the index range scan and index fast full scan.

We have a table cust and created index on column last_name. Now check the query Explain plain with SET AUTOTRACE ON feature of SQLPLUS.

Index range scan:
It will use the index range scan for where clause used on LAST_NAME column of the table CUST. It will choose INDEX RANGE SCAN for where clause. Plan choose INDEX RANGE SCAN because it will return the specific row based only on that index as shown in following explain plan.

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:
It will use the index fast full scan because the following query go through all the rows thats why it will choose INDEX FAST FULL SCAN for query. It will count all rows of the table with help of index created as shown in following execution plan.

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

Example:
In this query we have to return first_name also which is not present in the index, Index CUST_IDX1 is only created on LAST_NAME column, if you see first example it only use INDEX_RANGE_SCAN but for fetching the FIRST_NAME it will get ROW_ID from index and fetch the complete row by using that ROW_ID from INDEX and return the result.

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

More Details:
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.