Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

SELECT * 
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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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