Oracle using sub-optimal index on bind variables

Question

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.

• a1ex07: 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/…) .
• RDFozz: 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.
• Derek Pressnall: Sorry, I mis-typed that last part -- it should be ORDER_NUMBER != :BIND_VARIABLE_3. Thanks for pointing it out.
• Mark Stewart: 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?

Answer 1

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.

Answer 2

setting up a test cases on my side (oracle 19), it's using the customer_id index properly .. so not sure if there's more to your setup. Can you provide create statements that reproduce issue?

On another note, I tried this: (just a small data set test) ..

create table orders
 ( order_number number,
 order_date date,
 customer_id number
 );
 
create unique index order_number_I1 on orders ( order_number );
create index order_number_I2 on orders ( order_date );
create index order_number_I3 on orders ( customer_id );
-- fake some data
insert into orders
 select level order_number,
 sysdate - (level/5) order_date,
 trunc(dbms_random.value(1,99999)) customer_id
 from dual
 connect by level < 100000
/
commit;
-- pick a random customer to "test" logic 
select *
from orders
where customer_id = 86954
order by order_date
/
ORDER_NUMBER ORDER_DATE CUSTOMER_ID
------------ -------------------- -----------
 95259 22-apr-1971 14:32:33 86954
 86626 13-jan-1976 04:56:33 86954
 81076 27-jan-1979 04:56:33 86954
 66946 23-oct-1986 04:56:33 86954
 61663 13-sep-1989 19:20:33 86954
 54731 01-jul-1993 04:56:33 86954
6 rows selected.
with w_d as
 ( select *
 from orders
 where order_number = 61663
 )
select o.*
from orders o,
 w_d d
where o.customer_id = d.customer_id
 and o.order_date < d.order_date
order by o.order_date
/
ORDER_NUMBER ORDER_DATE CUSTOMER_ID
------------ -------------------- -----------
 95259 22-apr-1971 14:32:33 86954
 86626 13-jan-1976 04:56:33 86954
 81076 27-jan-1979 04:56:33 86954
 66946 23-oct-1986 04:56:33 86954
4 rows selected.

Yes, I just used hardcoded value there - mostly for just testing logic. I did run explain on this query, and yours, using binds ... in both cases, they seemed to use the customer_id index "properly".

I'd suggest trying a change in style, what does the above query do on your side? ie by just giving it the ORDER_NUMBER of the target order, and letting Oracle pick out/join the date/customer ... does that affect the explain results on your side any?

That query was written based on your explanation of what you're really after.

that is:

Edit: the purpose of this query is to find all previous orders by this particular customer,

So really, the only bind value we need is customer ;)