Index Range Scan And Fast Full Index Scan

INDEX RANGE SCAN

An index range scan is used to access a selected range of data. In this case not only the index but also the data from actual table is also fetched. Here, Oracle traverses through the branches of the index tree to reach the leaf block where it finds the first relevant value. Once it gets the first relevant value, it fetches the data from table rows from the rowid specified in the leaf block. Now using the pointers Oracle checks the next relevant leaf block that falls in the range and fetches the table rows similarly.

Below query uses Index Range Scan.

SQL> select age from list where age < 53;

The range specified here can be bounded(on both sides) or unbounded(one or both sides). This scan returns values in ascending order of index columns. The actual data returned need not be in order. If required data needs to ordered using the ‘ORDER BY’ clause.

FAST FULL- INDEX SCAN

Fast Full Index scans is used when Oracle just needs to access index without going to the actual data table. See the below query. It just needs the index value and not the actual  data in the table.

select distinct fruit , count(*) from market group by fruit;

Though only indexes are touched, Oracle needs to traverse through all the blocks of the index, which would be costlier. So to reduce the cost it uses the parameter db_file_multiblock_read_count to fetch multiblocks in a single I/O. This parameter needs to be set explicitly. The default value is 8. This operation can be parallized which again makes this scan faster and less costlier.

INDEX_FFS is the hint used to force Fast Full-Index Scan.

Article by : Divya