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)