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:
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.
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.
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.