why does delete take longer than insert

Question

Why executing delete statement takes much longer than insert?

As far as I know, delete triggers index and redo change as well as data block. So does delete.

Thus I thought two statements would have similar execution time on the same table. But totally different. What makes this difference?

For reference the dbms vendor is Oracle. The table has no triggers and two indexes are binded.

It's just a simple delete Delete, where cdate>201411.

Answer 1

There are a couple of reasons why DELETEs can take longer than inserts:

The main one is that DELETE is a query: the database has to locate the records you want to delete. Your statement is

"Delete, where cdate>201411."

If CDATE is indexed that's an index range scan at best. If it's not indexed then its a full table scan. But if CDATE has a poor clustering factor an indexed read might not perform as well as a full table scan anyway. Tuning a DELETE is a lot like tuning a SELECT statement.

Either way that's a lot more reading than inserting the record requires. Why does the database do this? Because of the need for undo. When we rollback the database uses the information in the UNDO tablespace to reverse our statement. For INSERT the undo action is a deletion, so all it needs is the ROWID of the inserted row. Whereas reversing a DELETE requires re-insertion of the record, so the whole row must be stored in UNDO.

So the DELETE action has to retrieve the whole row and store it in UNDO. The longer your record the more of an overhead the UNDO management becomes. (By contrast, the ROWID is a tiny and fixed overhead when inserting.)

Similarly, (as @lalitKumar points out ) the REDO log volumes can be much larger for deletes. OraFAQ has some interesting volumetrics here.

Foreign keys can affect insert and deletions: inserts have to check for the presence of keys in referenced tables whereas deletes have to check for references in dependent tables. This compares unique key look-ups against non-unique or even un-indexed columns.