I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows.

On this table, I have the index I_LOC_NAME_DATE, such that:

Column          Position  Descend
Location        1         ASC
Name            2         ASC
DateMovedIn     3         ASC

I'm writing a query which selects the customer's who moved into their location between two dates;

FROM   Customer 
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

From what I've read about Oracle's use of indexes, in this situation I would expect it to use a skip scan (filter by Location, and then hop betwen the names and get each move in date), but it turns out it uses a range scan.

An EXPLAIN reveals the range scan cost to be 77, and when I apply a index_ss hint as follows, the cost jumps to 401,096 (yes, really).

SELECT /*+ index_ss(Customer I_LOC_NAME_DATE) */ * 
FROM   Customer 
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

Can anyone explain how Oracle is using a range scan in this circumstance without the Name being specified?

2 Answers 2


You have a leading column specified in condition (Location = :location) . From here :

The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
col1 = :b1


It uses a range scan because location is the first field in the composite index. It's cheaper (according to its stats) to get all matching index records for that location, and it possibly then filters the index results on the date range before retrieving table data.

A skip scan would be considered if you were querying on name and datemovedin, or just on datemovedin.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.