Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Improve this querry!?
> 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_ISNWHERE
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