Home » SQL & PL/SQL » SQL & PL/SQL » Simple delete step seems to hang
Simple delete step seems to hang [message #19676] Wed, 03 April 2002 08:54 Go to next message
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
Re: Simple delete step seems to hang [message #19684 is a reply to message #19676] Wed, 03 April 2002 11:52 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
try this:
delete customer
where row_id in (select row_id from customer_stage );
Previous Topic: Dynamic SQL and Cursor
Next Topic: PL/SQL Packages & Dynamic SQL
Goto Forum:
  


Current Time: Fri Mar 29 03:49:01 CDT 2024