| Query takes too long [message #53503] |
Tue, 24 September 2002 15:34  |
Anand
Messages: 161
Registered: August 1999
|
Senior Member |
|
|
Hi I am have trying to run an update query which looks like this
UPDATE s02
SET inventory_type='A'
WHERE itemid7 IN (SELECT itemid7 FROM pritemA);
The table S02 has 19 Million records.
The table pritemA has 300 records.
Both the tables are indexed by the itemid7 field
This query has been running for over 5 hours.Is this common or is there a problem
I cant figure out what I am doing wrong.
Is there some kind of a diagnostic to figure out what is going. I tries Explain Plan . Could not interpret much from it.
This is a one time update.. Should i just let it run.
I have a few more updates I need to do the table which are quite similar.... What can I do to ensure their execution.
Thanks.. appreciate help
Hard_Pressed for time !!
|
|
|
|
| Re: Query takes too long [message #53522 is a reply to message #53503] |
Wed, 25 September 2002 11:23  |
Suresh
Messages: 189
Registered: December 1998
|
Senior Member |
|
|
Try using EXISTS. Since your subquery returns only few rows and assuming that most of your parent query rows are updated, try using the following.
UPDATE s02
SET inventory_type='A'
WHERE exists (SELECT * from FROM pritemA where itemid7 = s02.itemid7);
|
|
|
|
| Re: Query takes too long [message #53550 is a reply to message #53503] |
Thu, 26 September 2002 09:56 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
|
Try using an index on itemid7 if you do not have one. It has to loop through each time it finds a match so an index lookup may be much faster. Worth a try. Make sure you run analyze on the tables and indexes.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
