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