Re: Improve this querry!?

From: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Wed, 12 Nov 2003 07:55:03 -0500
Message-ID: <kI2dnSHlxsp_sC-iRVn-iQ@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)
2. what pct% of rows are being returned? that has a definite bearing on what to shoot for
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)
4. your subquery looks suspicious -- returning a value from the outer query, not correlated -- double negative logic -- searching on NULL logic

here's your query (reformatted by TOAD)

SELECT /*+ index(med_mt_tb ix0101) */

       med.inputserialnumber
      ,inputconnectionnumber
      ,rejectedmessage
      ,destinationnumber
      ,mrdstatus
      ,medstatus
  FROM med_mt_tb med -- parent records
      ,mrd_mt_tb mrd -- child records
 WHERE mrd.inputserialnumber = med.inputserialnumber(+)

   AND med.inputserialnumber > HEXTORAW('3FA6CDF541000013')    AND med.inputserialnumber < HEXTORAW('3FA6DEF541000013')    AND medstatus <> 'T'
   AND med.inputserialnumber NOT IN (

     SELECT /*+ index(mrd_mt_tb ix0101) */
                               med.inputserialnumber
                          FROM mrd_mt_tb
                         WHERE mrdstatus IS NOT NULL)

something like this might be more straightforward:

SELECT
    ...
  FROM

       med_mt_tb med -- parent records
      ,mrd_mt_tb mrd -- child records
     ,(SELECT  inputserialnumber
       FROM   mrd_mt_tb
       WHERE med.inputserialnumber > HEXTORAW('3FA6CDF541000013')
       AND med.inputserialnumber < HEXTORAW('3FA6DEF541000013')
       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('3FA6CDF541000013')
   AND med.inputserialnumber < HEXTORAW('3FA6DEF541000013')

   AND medstatus in ('X','Y','Z') -- <====== this allows index usage    AND mrd.inputserialnumber = null_status.inputserialnumber

let me know if this helps

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Ecce Nihil" <sct_at_picknowl.com.au> wrote in message
news:96ce817c.0311112043.ffb6e17_at_posting.google.com...


> Mark.Schubert_at_MyRealBox.com (Mark Schubert) wrote in message

news:<9c9c6d17.0311071045.19c8d337_at_posting.google.com>...


> > 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

>
>


> Try this...

>


> SELECT /*+ ORDERED USE_HASH(med) */

> med.inputserialnumber, inputconnectionnumber, rejectedmessage,

> destinationnumber, mrdstatus, medstatus

> FROM mrd_mt_tb mrd,med_mt_tb med

> WHERE

> mrd.inputserialnumber = med.inputserialnumber(+) AND

> med.inputserialnumber > HEXTORAW('3FA6CDF541000013') AND

> med.inputserialnumber < HEXTORAW('3FA6DEF541000013') AND

> medstatus <> 'T' AND

> med.inputserialnumber NOT IN (

> SELECT /*+ HASH_AJ */

> med.inputserialnumber FROM mrd_mt_tb

> WHERE mrdstatus IS NOT NULL)

>


> Note I have swapped the order of the tables as I am assuming size

> med<mrd.

> Don't be worried if this takes the same or more time than using

> indexes for 73min, it won't linearly scale up for the full database,

> whereas if you stick to using indexes you will probably find the time

> is 15min*7days/73min.

Received on Wed Nov 12 2003 - 06:55:03 CST