Index Range Scans

A Range Scan is any scan on an index that is not guaranteed to return zero or one row. ie. A SQL that uses a Unique index and supplies every column in the unique index in an equals clause will result in a Unique Scan, anything else is a Range Scan.

We can tell if Oracle is using a Range Scan in Explain Plan by the presence of a line similar to the following:

[UNIQUE] INDEX RANGE SCAN INDEX_NAME

The problem is that it is impossible to tell how many rows the range scan is scanning. A range scan that scans 5 rows on average will probably be fairly efficient. However a range scan scanning 10000 rows on average will probably be causing grief.

Inefficient range scans can have a number of causes:

Low cardinality index key
Use of range predicates such as <, >, LIKE, and BETWEEN.
Partial use of a concatenated index

Low cardinality index keys

If an index has poor cardinality (ie. more than 4% rows with the same index key) then it will perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES has an index on the column PAYMENT_METHOD which can contain values such as COD, CREDIT, CHEQUE, CASH. The statement

SELECT *
FROM   sales
WHERE  payment_method = 'CASH'

will probably perform so badly that you are better off without the index.

Range Predicates

Oracle can perform index scans when you use comparison operators =, <[=], >[=], LIKE, or BETWEEN. With all except =, Oracle can return a range of diffent index key values. For example, consider an index on sales.sale_date. The statement:

SELECT *
FROM   sales
WHERE  sale_date between '01-JAN-2003' and '31-DEC-2003'

can use the index (with a range of an entire year), but it is going to scan so many rows that it would be quicker to perform a full table scan.

A special case of the Range Predicates problem involves Range Keyed Tables, where the primary key of a table is a concatenated low and high value representing a range.

Partial use of a concatenated index

If your index has 2 or more columns (ie. a concatenated - or composite - index), then you don't need to scan on all of the columns in the index. Any query that does not use every column in the concatenated index with an = comparison will result in a range scan. See Concatenated Indexes for more information on problems with this type of index.