Re: Improve this querry!?

I assumed that all the select columns except mrdstatus came from med_mt_tb. If this is not true, please indicate the schema of the two tables.

• Dave

Dave Hau wrote:
> -- 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