Simple delete step seems to hang [message #19676] |
Wed, 03 April 2002 08:54 |
BobV
Messages: 1 Registered: April 2002
|
Junior Member |
|
|
I am running the following delete step:
delete from customer a
where exists (select row_id
from customer_stage
where row_id = a.row_id
)
and a.row_id >= 1
and rownum < 5
I have included the last part "and a.row_id >= 1" so that it will use the index (unique row_id).
The customer table has 2.5 million records and the stage table has about 10,000 records.
The problem that I'm having is performance. I eventually kill the process after 30+ minutes. I even added the last part "and rownum < 5" as a test, and it finally completed after 8 1/2 minutes!!. Why, if I am only going to be deleting a mere 5 records, does this seem to hang for so long? I did the showplan, and the unique index is being used (on the large table). Could the problem be with my db? Can someone offer some advice either in re-writing my sql, or with performance in general
Thanks in advance!
BV
|
|
|
|