Re: delete is getting hung when there is a huge data in table

On Wed, May 6, 2015 at 1:56 AM, Mitu Verma <mitu(dot)verma(at)ericsson(dot)com> wrote:
> Thank you so much all of you.
>
> Table audittraillogentry have PRIMARY KEY and FOREIGN KEY defined, below is the detail of existing table audittraillogentry.
>
> As you can see ., it is referenced by 2 tables , "cdrdetails" and "cdrlogentry" . "cdrdetails" table do not have the index whereas "cdrlogentry" has the index.
> Now after creating the index on "cdrdetails", deletion has become fast, 12 lakh records are deleted in 16 minutes, which is a drastic improvement in performance.
> Before indexing deletion of 500 records were taking ~2 minutes.

Yeah, this (unindexed foreign key causing slow deletes) is probably
the #1 performance gotcha in SQL.

If you're often doing very large deletes, sometimes it can help to
attempt to work out a better strategy, perhaps one of:

*) using TRUNCATE...CASADE
*) table partitioning organized such that you can drop a partition to
delete rows
*) temporarily disabling RI during large deletes (can be dangerous and
but in certain limited cases can be useful).

merlin