Re: Improve this querry!?

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Fri, 07 Nov 2003 21:28:34 GMT
Message-ID: <3FAC0E81.5090803@nospam_netscape.net>

• Substitute the owner of the tables for 'scott' in the
• following two statements

exec dbms_stats.gather_table_stats('scott', 'med_mt_tb');

exec dbms_stats.gather_table_stats('scott', 'mrd_mt_tb');

select inputserialnumber, inputconnectionnumber, rejectedmessage, destinationnumber, null as mrdstatus, medstatus from med_mt_tb

where inputserialnumber > to_number('3fa6cdf541000013', 'xxxxxxxxxxxxxxxx')

and inputserialnumber < to_number('3fa6def541000013',

'xxxxxxxxxxxxxxxx')

and medstatus <> 'T'
and inputserialnumber in
(select inputserialnumber
from mrd_mt_tb
group by inputserialnumber
having count(mrdstatus) = 0);

HTH,
Dave

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
Received on Fri Nov 07 2003 - 15:28:34 CST