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:

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

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

©Copyright 2003