Re: Improve this querry!?

From: Jan van Mourik <jmourik_at_yahoo.com>
Date: 7 Nov 2003 19:57:21 -0800
Message-ID: <ffe966de.0311071957.1d161318@posting.google.com>

Cool Dave...

In the original query there's an outer join, maybe Mark needs something like this (slight adaption of Dave's solution):

select med.inputserialnumber, med.inputconnectionnumber, med.rejectedmessage,
 null as mrdstatus, medstatus
from med_mt_tb med
, (select inputserialnumber, count(mrdstatus) ct

     from mrd_mt_tb
     group by inputserialnumber
     ) mrd

where med.medstatus <> 'T'
and med.inputserialnumber = mrd.inputserialnumber(+) and nvl(mrd.ct,0) = 0
;

I tested with two tables with each 1mil rows, Dave's query runs in 16s, this outer join query in 21s.
Without the outer join it runs in 13s:

select med.inputserialnumber, med.inputconnectionnumber, med.rejectedmessage,
 null as mrdstatus, medstatus
from med_mt_tb med
, (select inputserialnumber

     from mrd_mt_tb
     group by inputserialnumber
     having count(mrdstatus) = 0
     ) mrd

where med.medstatus <> 'T'
and med.inputserialnumber = mrd.inputserialnumber
;

jan

om mani padme hum Received on Fri Nov 07 2003 - 21:57:21 CST