Re: Complex SQL performance question

Hello Tim,

Did you try a join with the Codes table :

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 = c.code_id -- join with Codes table  and c.code_type = 'MYCODE' --
 and od.create_dtt between sysdate-365 and sysdate

This'll do nested loops, usually faster then IN or EXISTS (depending on resultset). Make sure the 3 tables are analyzed !

cheers

     Roelof Schierbeek

R.Schierbeek, DBA
e-mail: info_at_NSPMbytelife.com

TEL : 06 2409 6983
"Tim Smith" <timasmith_at_hotmail.com> schreef in bericht news:a7234bb1.0309271225.51a7185_at_posting.google.com...
> 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 Sun Sep 28 2003 - 09:45:44 CDT