OK, let’s talk about the Index Range Scan. The word range reflects that Oracle will scan through (a part) the index. As opposed to an Index Unique Scan where Oracle exactly knows what to look for and where, it just decends through the tree-structure.
The Index Range Scan does not know how many rows will be returned (0, 1 or more). Just as in the post for the Index Unique Scan I will use this setup. In short: a table with (among others) a column N with integers. There is a index on this column. It actually is a unique index, but for this demonstration that does not matter.
Let’s see this index in action:
(2020-10-31 19:53:52) MARTIJN@_DUN > explain plan for select * from index_demo where n>90000;
(2020-10-31 19:54:28) MARTIJN@_DUN >
(2020-10-31 19:54:29) MARTIJN@_DUN > select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1600936347
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 371K| 82 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| INDEX_DEMO | 10000 | 371K| 82 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_DEMO_U_N | 10000 | | 22 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N">90000)
(2020-10-31 19:54:38) MARTIJN@_DUN >
And there you have it, an Index Range Scan. When there is a suitable index Oracle can use this access method.
One more thing to notice: line 2 in the explain plan. Oracle will try to batch (as opposed to one-by-one) the reading of the rows as much as possible, to be as fast as possible.