| Improve this querry!? [message #9387] |
Fri, 07 November 2003 09:59  |
Mark Schubert
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
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)
/
|
|
|
|
| Re: Improve this querry!? [message #9407 is a reply to message #9387] |
Sat, 08 November 2003 17:47  |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
> AND med.inputserialnumber NOT IN (
> SELECT /*+ INDEX(MRD_MT_TB IX0101) */
> med.inputserialnumber FROM mrd_mt_tb
> WHERE mrdstatus IS NOT NULL)
Maybe that should be:
AND med.inputserialnumber NOT IN (
SELECT inputserialnumber FROM mrd_mt_tb
WHERE mrdstatus IS NOT NULL)
/
Using med. the subquery becomes a correlated one. I think. Actually I'm not quite sure what it will do with an attribute of the parent query in its SELECT list but not in its WHERE clause. In any case I can't see the hint doing anything.
|
|
|
|
| Re: Improve this querry!? [message #9427 is a reply to message #9387] |
Mon, 10 November 2003 08:21 |
Mark Schubert
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
I agree - However, I was thinking (this always gets me in trouble) that by specifying med - I would further limit the inner query to only the med from within my specified time from the outer query. Otherwise, if the inner query is done first, he would have to scan the entire mrd table and would get ALL mrd records where mrdstatus <> NULL.
Maybe if that's the case I should specify the same time range limitation in the inner query as well???
|
|
|
|
| Re: Improve this querry!? [message #9428 is a reply to message #9407] |
Mon, 10 November 2003 08:23 |
Mark Schubert
Messages: 3
Registered: November 2003
|
Junior Member |
|
|
I agree - However, I was thinking (this always gets me in trouble) that by specifying med - I would further limit the inner query to only the med from within my specified time from the outer query. Otherwise, if the inner query is done first, he would have to scan the entire mrd table and would get ALL mrd records where mrdstatus <> NULL.
Maybe if that's the case I should specify the same time range limitation in the inner query as well???
|
|
|
|
| Re: Improve this querry!? [message #9436 is a reply to message #9428] |
Mon, 10 November 2003 17:36 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
There is no implicit linking behaviour - if you need to limit the subquery then specify this in the WHERE clause.
If you need all mrd records where mrd status is not null then a full scan might even be a good approach. See what the optimiser comes up with. It's best not to force the database to do something unusual until you are sure you have to.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
