Index Range Scans

Optimizer chooses Index Range Scans for selective queries which have bounded as well as unbounded data.

Thus for every predicate clause having >,>=,<,<= condition on an indexed column, Oracle optimizer would go for Index Range Scans unless fetching large amount of data or unselective query which favours FTS.

Following are the situations where optimizer chooses Index Range Scans:-

  • One or more leading columns of an index are specified in conditions.
  • 0, 1, or more values are possible for an index key.
SQL_ID  bxz5abvytw9tq, child number 0
-------------------------------------
SELECT * FROM   employees WHERE  department_id = 20 AND    salary > 1000
 
Plan hash value: 2799965532
 
-------------------------------------------------------------------------------------------
|Id | Operation                           | Name             |Rows|Bytes|Cost(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                    |                  |    |     | 2 (100)|        |
|*1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |  2 | 138 | 2   (0)|00:00:01|
|*2 |   INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX|  2 |     | 1   (0)|00:00:01|
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SALARY">1000)
   2 - access("DEPARTMENT_ID"=20)

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