Hello
2011/12/19 Daniel Migowski <dmigowski@ikoffice.de>:
> 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:
>
you can disable check per session if you need
ALTER TABLE ... DISABLE TRIGGER ALL;
Regards
Pavel Stehule
>
>
> 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