Re: Complex SQL performance question

Tim Smith wrote:

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

Excuse me but didn't you post this in a non-Oracle usenet group too? Seems I've read this before.

Anyway no one can help you because the answer to your question is version dependent (you didn't
think it imortant to specify version), optimizer dependent (you didn't specify optimizer mode), data
distribution dependent, and there are no hard and fast rules other than use EXPLAIN PLAN, TKPROF,
and verify verify verify.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)