The index range scanplan operation is used in non-unique index. It returns the rowids of the searched value in the ascending order of the indexed value.
An index range scan accesses the data in the index with single block IO because leaf blocks are not stored in the order of the indexed keys they contain.
In most cases, index range scan does not have a child operator. Some child operations I've come across include
create table tq84_tab(
nm number,
tx varchar2(200)
);
insert into tq84_tab
select
round(dbms_random.value(1, 1000), 1),
rpad('x', 200)
from
dual connect by level <= 100000
;
create index tq84_tab_ix on tq84_tab(nm);
begin
dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/
explain plan for
select
tx
from
tq84_tab
where
nm between 100 and 105;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -----------------------------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_TAB |
-- | 2 | INDEX RANGE SCAN | TQ84_TAB_IX |
-- -----------------------------------------------------------
drop table tq84_tab;
See also
Compare INDEX RANGE SCAN with INDEX UNIQUE SCAN which returns at most 1 ROWID for a searched value.
select
sest.sid,
sest.value
from
v$sesstat sest join
v$statname stat on sest.statistic# = stat.statistic#
where
stat.name = 'index range scans'
;