How to Oracle use a Range Scan here

indexoracle

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?

Best Answer

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

Related Solutions

Why is Oracle using the index here

I remember seeing that Oracle will not use the index and perform a full table scan instead if it's retrieving more than 5% or so of all rows.

This is a "rule of thumb" and shouldn't be taken as a prediction. The Oracle CBO chooses an execution plan based on the estimated 'cost' of the options. The estimated cost will depend on various parameters, and the complexity increases with each Oracle release.

You can use hints and explain plan to get a more detailed idea of the relative cost of two plans - but this should be done in a single query because the 'cost' is not guaranteed to be an absolute measure outside of a single plan:

select /*+ FULL(foo) */ * from foo where group_id=10
union all
select /*+ INDEX(i_foo) */ * from foo where group_id=10;

example explain plan with relative costs

Is this the way it's supposed to be?

In short: Yes.

Unless you have identified a particular performance issue you should trust the CBO to choose the correct path (and not, for example, use hints except for testing and experimenting like above). If you have identified a problem, the next step is to start investigating whether the CBO is making incorrect assumptions and how to give it better information - rather than assuming it is broken and/or trying to circumvent it.

Postgresql – Why PostreSQL 9.2 does not use index scan if it can

PostgreSQL only supports index-only scans since 9.2 and only if the visibility map allows that.

If there is but a single concurrent transaction which has updated a record in a page (and hence there exists a record not visible to all transaction), the visibility map record for this page is invalidated and its records need to be checked for visibility (requiring a heap fetch).

A heap fetch is quite an expensive procedure and it's impossible to tell now many of them would be required on the optimization stage.

So optimizer just tries to play safe and chooses the more predictable path.

Related Question