Re: Improve this querry!?

'AND medstatus NOT in ('T')' will very likely not use an index

there's no reason why
'AND medstatus like 'S%')' won't work -- looks like you weren't using the 'LIKE' operator, but the IN operator

1. try LIKE 'S%'
2. post the plan
   • mcs

"Mark Schubert" <Mark.Schubert_at_MyRealBox.com> wrote in message news:9c9c6d17.0311121451.6a5fe8f9_at_posting.google.com...
> Thanks for your reply!!
>
> "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message
news:<kI2dnSHlxsp_sC-iRVn-iQ_at_comcast.com>...
> > might be a little late in this response but here's some observations
> > (hopefully they aren't redundant of prior posts):
> >
> > 1) did you supply the explain plans in another post? they would be
helpful
> > (sorry if i missed them)
>
> No - but I will if you need them.
>
> > 2) what pct% of rows are being returned? that has a definite bearing on
what
> > to shoot for
>
> I expect 0 rows to be returned. We are searching for a specific
> condition which SHOULD NOT exist.
>
> > 3) do you have control over the table design? specifically:
> > -- can medstatus <> 'T' be replaced with a positive expression (i'm
assuming
> > the '<>' is not using indexes)
>
> It's a production database and I cannot modify it or transfer the data
> to a test database (it's too large). The problem with medstatus is if
> it's not set to 'T', it can have a value of 'S!^&@' where the 'S' is
> always the first character, but the 4-5 characters following are
> random. So - I tried something like medstatus in ('I', 'S%', 'N') -
> but it doesn't work. The S% doesn't work.
>
> > 4) your subquery looks suspicious
> > -- returning a value from the outer query, not correlated
> > -- double negative logic
> > -- searching on NULL logic
>
> I figured if I used the med hint it would be limited to the outer
> query limitations - but I'm told that's not true. I thought I tested
> that (leaving it off) and it made a difference, but I can't swear to
> that. I'm pretty sure it did though.
>
> And the reason for the double negative logic is 90% of the mrdstatus
> are = NULL!
>
> Here is the query I ran implementing your suggestion
>
> SELECT
> med.inputserialnumber
> ,inputconnectionnumber
> ,rejectedmessage
> ,destinationnumber
> ,mrdstatus
> ,medstatus
> FROM
> med_mt_tb med -- parent records
> ,mrd_mt_tb mrd -- child records
> ,(SELECT inputserialnumber
> FROM mrd_mt_tb
> WHERE inputserialnumber > HEXTORAW('3FB0268000000000') -- 11
> NOV 00:00
> AND inputserialnumber < HEXTORAW('3FB028D800000000') -- 11 NOV
> 01:00
> GROUP BY inputserialnumber
> HAVING count(mrdstatus) = 0
> ) null_status -- <=== this returns a list of sn's where all
> status values are null
> WHERE mrd.inputserialnumber = med.inputserialnumber(+)
> AND med.inputserialnumber > HEXTORAW('3FB0268000000000') -- 11 NOV
> 00:00
> AND med.inputserialnumber < HEXTORAW('3FB028D800000000') -- 11 NOV
> 01:00
> AND medstatus NOT in ('T') -- <====== this allows index usage
> AND mrd.inputserialnumber = null_status.inputserialnumber
> /
>
> Your query took 90+ seconds. The same as mine. Here is a query
> implementing two other suggestions that takes only 24 seconds - but is
> still too slow.
>
> SELECT /*+ HASH_AJ */
> med.inputserialnumber, inputconnectionnumber, rejectedmessage,
> destinationnumber, mrdstatus, medstatus
> FROM mrd_mt_tb med, med_mt_tb mrd,
> (SELECT
> inputserialnumber
> FROM mrd_mt_tb
> GROUP BY inputserialnumber
> HAVING (COUNT(mrdstatus)=0)) INNER_ISN
> WHERE
> INNER_ISN.inputserialnumber=med.inputserialnumber AND
> INNER_ISN.inputserialnumber=mrd.inputserialnumber AND
> med.inputserialnumber > HEXTORAW('3FB0268000000000') AND --
> Mon Nov 11 00:00:00
> med.inputserialnumber < HEXTORAW('3FB028D800000000') AND --
> Mon Nov 11 01:00:00
> medstatus <> 'T';
> /
Received on Wed Nov 12 2003 - 17:07:01 CST