| To improve execution time of query [message #288296] |
Sun, 16 December 2007 23:29  |
donind
Messages: 95
Registered: February 2007
|
Member |
|
|
Hi,
Below is the query takking too much time in deleting records.
[CODE]
DELETE FROM tab1 a
WHERE exists (SELECT 1 FROM tab2 b
WHERE a.col1 = b.col2);
[CODE]
The columns col1 and col2 are indexed. The select subquery is returning 6 million rows.
Its taking 4 minutes for deleting all these records.
Is there any efficient way writing the above query so that the performance can be increased.
Thanks in advance
|
|
|
|
|
|
| Re: To improve execution time of query [message #288314 is a reply to message #288298] |
Mon, 17 December 2007 01:08  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
So the sub-query returns 6M rows. Does that mean it DELETEs 6M rows? If so, I agree with Michel. I'm actually a bit impressed that it can delete 6M rows in 4 minutes. That's pretty fast.
If you are deleting much, much fewer rows (say, < 100,000), then you may be using indexes inappropriately.
Tell us how many rows in BOTH tables, and how many rows will be deleted.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
