> Each value has 1/13th of the table, which is too many rows per value to
> make an IndexScan an efficient way of deleting rows from the table.
But, the original question was that the delete that was taking a long time
was on a different table. I tried to delete 150 rows from a table with 750
rows, which is FK referenced from this large table. If I understand
correctly, Tom suggested that the length of time was due to a sequential
scan being done on the large table for each value being deleted from the
small one.
(I have no formal training in database administration nor database theory,
so please excuse me if I am being dumb.)
For this FK check, there only need be one referring id to invalidate the
delete. ISTM that for any delete with a FK reference, the index could
always be used to search for a single value in the referring table
(excepting very small tables). Why then must a sequential scan be
performed in this case, and/or in general?
--
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University, Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221
