Hi,
I face the following problem: I have a large table with 12 million addresses, referenced by 20 other tables (some containing about one million entries). There are indexes on the foreign keys.
Now I wanted to delete about 10 million addresses (that are not referenced anymore from anywhere), and have a statement like:
DELETE FROM address
WHERE id NOT IN (SELECT address_id FROM bank where address_id IS NOT NULL)
AND id NOT IN (SELECT poboxaddress_id FROM bank where poboxaddress_id IS NOT NULL)
AND id NOT IN (SELECT address_id FROM bankconnection where address_id IS NOT NULL)
...lots more...
This takes more than 10 hours here (I had to cancel the statement).
I have two suggestions:
1. Currently for each row to be deleted, a SELECT is done in each column referencing the deleted entry. This takes really a lot of time. It is possible to check in an elegant way if an entry can be deleted, like in the above query. I know it is not easy to autocreate such a statement, but this would make deletions much faster.
2. I would have loved a special option “UNREREFENCED” given to the delete statement, so all rows referenced from anywhere would automagically be excluded from my delete statement. When this keyword is given, no FK checks have to be done, because FK referenciality cannot be violated anyway.
DELETE UNREFERENCED FROM address WHERE …;
Thanks for your time and this great database product.
Regards,
Daniel Migowski