Oracle performance - disabling FKs to make DELETE statement work faster?

When deleting from a large table in Oracle - let's call it table X - does it make sense to disable table X's FKs that do not have ON DELETE CASCADE? I'm not referring to disabling FKs on other tables that link to table X, but just disabling FKs on table X to improve the performance of the DELETE statements.

I'm making the indexes on table X unusabl开发者_Python百科e, but the DELETE still takes a while.

I think that those FKs don't matter to the performance of the DELETE statement since we're just deleting, and not inserting or updating, so the FKs don't need to be checked. What do you think?

That seems like a really bad idea. No matter what you do, you'll have a period where referential integrity is not enforced on your database. Then you go to put the FKs back in place and, oops, someone has inserted an invalid row.

Furthermore, ALTER TABLE is a DDL statement, so executing it will commit any work up to that point. You'll lose the ability to rollback if something goes wrong elsewhere in your transaction.

Can you look through the explain plan to see why your DELETE statement is taking so long?

Eventually I didn't have to disable those FKs before the archive process starts and enable them when the process ends. But instead, in order to improve performance of the DELETE statements, we had to drop indexes before the archive process starts and recreate them after the archive process finishes. We also committed more often.