Re: slow delete

Thanks so much for your help.

I can select the 80K data out of 29K rows very fast, but we I delete them, it always just hangs there(> 4 hours without finishing), not deleting anything at all. Finally, I select pky_col where cola = 'abc', and redirect it to an out put file with a list of pky_col numbers, then put them in to a script with 80k lines of individual delete, then it ran fine, slow but actually doing the delete work:

delete from test where pk_col = n1;
delete from test where pk_col = n2;
...

My next question is: what is the difference between "select" and "delete"? There is another table that has one foreign key to reference the test (parent) table that I am deleting from and this foreign key does not have an index on it (a 330K row table).

Deleting one row at a time is fine: delete from test where pk_col = n1;

but deleting the big chunk all together (with  80K rows to delete) always hangs: delete from test where cola = 'abc';

I am wondering if I don't have enough memory to hold and carry on the 80k-row delete.....
but how come I can select those 80k-row very fast? what is the difference   between select and delete?

Maybe the foreign key without an index does play a big role here, a 330K-row table references a 29K-row table will get a lot of table scan on the foreign table to check if each row can be deleted from the parent table... Maybe select from the parent table does not have to check the child table?

Thank you for pointing out about dropping the constraint first, I can imagine that  it will be a lot faster.

But what if  it is a memory issue that prevent me from deleting the 80K-row all at once, where do I check about the memory issue(buffer pool) how to tune it on the memory side?

Thanks a lot,
Jessica