Oracle using sub-optimal index on bind variables

index-tuningoracleoracle-11goracle-12c

Let's say you have a table, called ORDERS, which contains ORDER_NUMBER, CUSTOMER_ID, and ORDER_DATE (among other fields). And you have one index on ORDER_NUMBER, another on CUSTOMER_ID, and third index on ORDER_DATE.

The following query is intended to select all orders that are older than the current order, from the same customer: 

select ORDER_NUMBER from ORDERS
    where CUSTOMER_ID = :BIND_VAIABLE_1
    and ORDER_DATE <= :BIND_VARIABLE_2
    and ORDER_NUMBER != :BIND_VARIABLE_3

In this case, :BIND_VARIABLE_2 would be a recent date, usually today's date, from a current order. The problem is, that Oracle seems to want to use the index on ORDER_DATE, which selects almost the entire table, then filters on CUSTOMER_ID, whereas it is much more efficient to use the index on CUSTOMER_ID instead (since there are only a few orders per customer).

The thing is, if you do a query without bind variables, it will always use CUSTOMER_ID like it should. But with bind variables, Oracle creates a query plan assuming that ORDER_DATE will be some random value, so ends up using the less efficient index.

How can I get Oracle to use the correct index in the presence of bind variables, without using index hints (since I can't modify the app)? I've already tried re-computing statistics, using histograms, etc. but without much luck. Ideally I'd like to turn off bind-variable optimization altogether, and have Oracle re-formulate a query plan based on the actual contents of the bind variables.

Edit: the purpose of this query is to find all previous orders by this particular customer, so BIND_VARIALBE_1 is the current customer, BIND_VARIABLE_2 would be the date of the current order, and BIND_VARIABLE_3 is the current order number.

Best Answer

I would create a composite index on (CUSTOMER_ID, ORDER_DATE) and that should fix all your issues, assuming ORDER_DATE is a DATE data type. And assuming ORDER_DATE should not be null, ensure the table definition says NOT NULL. I am certain Oracle is not using "some random value" if you gathered statistics correctly. You tagged your question with both 11g and 12c; you running different versions on different instances?

Is your bind variable the correct data type for the ORDER_DATE? If this is a vendor application, you may need to create a function-based index on the ORDER_DATE column to produce a reasonable date that matches the format of the bind variable.

Related Question