2

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.

  • Side note: you don't have to modify the app to add hints. DBMS_SQLDIAG.CREATE_SQL_PATCH will do the job (blogs.oracle.com/optimizer/…) . – a1ex07 Aug 8 '17 at 18:55
  • Maybe I'm missing something (I don't usually work in Oracle), but why would you have CUSTOMER_ID = :BIND_VARIABLE_1 and CUSTOMER_ID != :BIND_VARIABLE_3 in the same WHERE clause. :BIND_VARIABLE_1 and :BIND_VARIABLE_3 would either be the same (in which case no rows would be returned), or different (in which case CUSTOMER_ID != :BIND_VARIABLE_3 serves no purpose). Wondering if this might be what's forcing the use of the ORDER_DATE index. – RDFozz Aug 9 '17 at 17:16
  • Sorry, I mis-typed that last part -- it should be ORDER_NUMBER != :BIND_VARIABLE_3. Thanks for pointing it out. – Derek Pressnall Aug 9 '17 at 17:55
  • Is ORDER_DATE defined as a DATE (or TIMESTAMP or similar date/time) type? Did some idiot use a mock value of 31-DEC-2525 to represent a null ORDER_DATE (but I would guess it is defined as NOT NULL). Is it defined as NOT NULL? – Mark Stewart Jul 20 '18 at 20:26
0

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.

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.