Complex SQL performance question

Hi,

   I have a table ORDER_DETAIL with 22 million rows which has an index of

(person_id, code_id, created_dtt)

   I have another ORDER table with 5 million rows which has an index of

(order_dtt, person_id)

   I have a small CODES table with 1000 rows which allows me to get the 50 or so codes I need. I want the codes entered over a date range. My query needs to be something like this:

select od.person_id, od.code_id
from order_detail od, order o, codes c
where o.order_dtt between sysdate-365 and sysdate  and o.person_id = od.person_id
 and od.code_id in (select code_id from codes where code_type = 'MYCODE')
 and od.create_dtt between sysdate-365 and sysdate  

But is this using the full index on the ORDER_DETAIL table? Should I be using EXISTS in some fashion instead?

Accessing the ORDER_DETAIL table is a pain because it is so large, the code_id's I need are a relatively small number but the date range is about 25% of the table - same with the ORDER table. However it is the only way I can get to filtering down to the code_id.

This is a simplification of the problem - but accurate - adding additional indexes is not an option.

thanks!

Tim Received on Sat Sep 27 2003 - 15:25:07 CDT