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.verma@ericsson.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
indexwhereas "cdrlogentry" has the index. 
> Now  after creating the index on "cdrdetails", deletion has become fast, 12 lakh records are deleted in 16 minutes,
whichis 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