Detecting Index Range Scans in Oracle

The index range scan access method still exists in Oracle 12c. It is one of the most common access methods used by the Oracle optimizer. An index range scan is used when the SQL statement contains a restrictive clause that requires a sequential range of values that are indexed for the table.

For example, the following query would use an index range scan:

SELECT * FROM employees 
WHERE department_id BETWEEN 20 AND 40;

The optimizer would choose an index range scan because it can efficiently access the rows in the table that satisfy the WHERE clause. The optimizer would first locate the first index entry for department_id 20. It would then scan the index entries sequentially until it reaches an index entry for department_id 40. The optimizer would then use the ROWID values in the index entries to retrieve the table rows. Index range scans can be very efficient, especially when the index is selective and the range of values is narrow. However, index range scans can become less efficient if the index is not selective or if the range of values is wide.
You can view the access paths that the optimizer chooses for your SQL statements by using the EXPLAIN PLAN feature. The EXPLAIN PLAN feature will show you the access path that the optimizer chose for each step in the query execution plan. If you see an index range scan in the EXPLAIN PLAN for your SQL statement, it means that the optimizer is using an efficient access method to retrieve the data that you requested.

The index range scan is one of the most common items found in an EXPLAIN PLAN. The Oracle SQL optimizer invokes the index range scan whenever Oracle detects that an index can be used to speed access to the table rows. Index range scans also help prevent sorting operations. If the SQL query can access the rows with an index that is in the same sequence as the ORDER BY clause, then Oracle will not need to sort the result set. This can cause dramatic performance improvement. Index range scans are always invoked whenever Oracle detects that an index can be used to avoid a sorting operation. For example, the following query will use an index on the STATE column to avoid a costly sort in the Oracle TEMP tablespace.

Select
   Customer_name,
   Customer_address
From
   Customer
Order by state;

In this case, Oracle will decide to use the STATE index, although this query would normally be serviced with a full-table scan. This is because the Oracle optimizer knows that it is faster to use an index than it is to perform a large sort in the TEMP tablespace. However, index range scans are more common when a range has been specified in the WHERE clause. View the code below.

explain plan for 
select 
   student_name,
   fiscal_aid 
from 
   student
where 
   fiscal_aid > 1000
;

Query Plan ----- -----  
SELECT STATEMENT [CHOOSE] Cost=1 
 TABLE ACCESS BY ROWID STUDENT [ANALYZED] 
  INDEX RANGE SCAN STUD_AID_IDX [ANALYZED]                                    

In order to invoke an index range scan, an index must exist, and the WHERE clause must specify the leading column for the index. What we have learned works great for small, simple EXPLAIN PLAN output, but what about the complex query that returns hundreds of lines of output? Let us look at an Oracle tool that will help us understand complex EXPLAIN PLAN output.

Before moving on to the next lesson, click the Exercise link below to practice analyzing an EXPLAIN PLAN output.
Index Range Scan - Exercise