Re: Improve this querry!?

Mark Schubert wrote:

[...]
>
> 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)
> /
>

Hello Mark,
maybe you gain some performance using a hash antijoin whith your NOT IN clause. Have a look at the docs. Since NOT IN is one of the slowest operations one can do with a database, this may boost your performance. The HASH_AJ hint has some prerequisites to work properly though.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de