Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Improve this querry!?

Re: Improve this querry!?

From: Mark Schubert <Mark.Schubert_at_MyRealBox.com>
Date: 12 Nov 2003 08:11:55 -0800
Message-ID: <9c9c6d17.0311120811.4cffb13f@posting.google.com>


> Note I have swapped the order of the tables as I am assuming size
> med<mrd.

That is correct - in fact the ratio is approx 1.8 mrd to 1 med.

This worked in less than 30 seconds!!!! And covers a 24 hour period!

EXCELLENT!! This is the answer I was looking for. FYI, I tried it without the inputserialnumber limitation, and it wouldn't work - rollback segment problem.

THANK YOU VERY MUCH for your reply and I now have a solution where I can cover 6-7 days worth of data in only a few minutes.

Mark.

SELECT /*+ ORDERED USE_HASH(med) */
  med.inputserialnumber, inputconnectionnumber, rejectedmessage, destinationnumber, mrdstatus, medstatus
  FROM med_mt_tb med, mrd_mt_tb mrd,
  (SELECT /*+ HASH_AJ */

     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('3FA59A8000000000') AND -- Mon Nov 3 00:00:00

   med.inputserialnumber < HEXTORAW('3FA6EC0000000000') AND -- Tue Nov 4 00:00:00

   medstatus <> 'T';
/

sct_at_picknowl.com.au (Ecce Nihil) wrote in message news:<96ce817c.0311112043.ffb6e17_at_posting.google.com>...
> 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 - 10:11:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US