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.

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
– a1ex07, Commented Aug 8, 2017 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
– RDFozz, Commented Aug 9, 2017 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
– Derek Pressnall, Commented Aug 9, 2017 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
– Mark Stewart, Commented Jul 20, 2018 at 20:26

Mark Stewart · Accepted Answer · 2018-07-20 20:31:40Z

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.

Ditto · Accepted Answer · 2023-06-20 13:55:08Z

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 ;)

Stack Exchange Network

Oracle using sub-optimal index on bind variables

2 Answers 2

Your Answer

Hot Network Questions

Oracle using sub-optimal index on bind variables

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions