Re: Improve this querry!?

Mark Schubert wrote:

>Is there any way to improve the efficiency of the following query?
>Currently, this query takes more than 15 min to complete (73 min of
>records) - and I have to check the whole database which is 7 days!!!
>(basically, this will take forever).
>
>I tried the query without the indexes and it's MUCH slower. It takes
>just less than a minute (55 secs) to check 5 min between
>inputserialnumbers. There must be a better way.
>
>Here is what I need. A query to return all inputserialnumber from
>either med or mrd where medstatus <> 'T' and ALL mrdstatus = NULL.
>med.inputserialnumbers are unique - mrd.inputserialnumbers are
>"1->many". To clarify, if at least 1 mrdstatus for
>mrd.inputserialnumber <> NULL, then I do not want that
>inputserialnumber.
>
>SELECT /*+ INDEX(MED_MT_TB IX0101) */
>med.inputserialnumber, inputconnectionnumber, rejectedmessage,
>destinationnumber, mrdstatus, medstatus
>FROM med_mt_tb med, mrd_mt_tb mrd
>WHERE
>mrd.inputserialnumber = med.inputserialnumber(+) AND
>med.inputserialnumber > HEXTORAW('3FA6CDF541000013') AND -- Mon Nov 3
>21:51:49 2003
>med.inputserialnumber < HEXTORAW('3FA6DEF541000013') AND -- Mon Nov 3
>23:04:21 2003
>medstatus <> 'T' AND
>med.inputserialnumber NOT IN (
>SELECT /*+ INDEX(MRD_MT_TB IX0101) */
>med.inputserialnumber FROM mrd_mt_tb
>WHERE mrdstatus IS NOT NULL)
>/
>
>
>Oracle Server 8.1.7.3
>IBM DYNIX/ptx
>
>

I could write a book on the subject you've asked. Lets start at the beginning.

What is a "min" when used in the context "73 min of records"?

But which optimizer mode are you using?
If CBO did you run DBMS_STATS to create current statistics for the optimizer?
Have you run Explain Plan?
What is the cardinality?
What percentage of records are being returns? Why haven't you patched to 8.1.7.4?
and on and on and on.

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