3

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.

4
  • 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, 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
    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. 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? Jul 20, 2018 at 20:26

2 Answers 2

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.