Re: Improve this querry!?

From: Ecce Nihil <sct_at_picknowl.com.au>
Date: 11 Nov 2003 20:43:24 -0800
Message-ID: <96ce817c.0311112043.ffb6e17@posting.google.com>

Mark.Schubert_at_MyRealBox.com (Mark Schubert) wrote in message news:<9c9c6d17.0311071045.19c8d337_at_posting.google.com>...
> 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

Try this...

SELECT /*+ ORDERED USE_HASH(med) */
med.inputserialnumber, inputconnectionnumber, rejectedmessage, destinationnumber, mrdstatus, medstatus
FROM mrd_mt_tb mrd,med_mt_tb med
WHERE

mrd.inputserialnumber = med.inputserialnumber(+) AND
med.inputserialnumber > HEXTORAW('3FA6CDF541000013') AND 
med.inputserialnumber < HEXTORAW('3FA6DEF541000013') AND 

medstatus <> 'T' AND
med.inputserialnumber NOT IN (
SELECT /*+ HASH_AJ */
med.inputserialnumber FROM mrd_mt_tb
WHERE mrdstatus IS NOT NULL)

Note I have swapped the order of the tables as I am assuming size med<mrd.
Don't be worried if this takes the same or more time than using indexes for 73min, it won't linearly scale up for the full database, whereas if you stick to using indexes you will probably find the time is 15min*7days/73min. Received on Tue Nov 11 2003 - 22:43:24 CST