Detecting Index Range Scans in Oracle

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