Re: Improve this querry!?

From: Mark Schubert <Mark.Schubert_at_MyRealBox.com>
Date: 12 Nov 2003 14:51:31 -0800
Message-ID: <9c9c6d17.0311121451.6a5fe8f9@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 - 16:51:31 CST