INSERT And DELETE EXECUTION Plan

Why Insert is faster than Delete?

•  If we are deleting any records Oracle have to search the values which we are deleting from the tablespaces, indexes etc. but while we are inserting any record oracle just place that record into the table name specified.
• Both insert and delete are used for different purposes. Also inserts are not always faster than delete. If the table has got indexes, then insert will be slow and delete will be fast (if the WHERE clause uses the indexed column). If the table does not have any indexes, then insert will be fast and delete will be slow.
• Insert is faster than delete because insert just add values to the table but delete keeps the logs in the memory for future references

Check the EXECUTION Plan for Insert & Delete
Set AUTOTRACE on and Insert Record into Table to see the Execution Plan.

SQL> INSERT INTO EMP_TEST Values(112,'New',200450,'Active',40,103);

1 row created.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |     1 |    28 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMP_TEST |       |       |            |          |
-------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          4  db block gets
          1  consistent gets
          0  physical reads
        316  redo size
        834  bytes sent via SQL*Net to client
        813  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now Delete above record and check the Execution Plan.

SQL> DELETE From EMP_TEST Where ID=112;

1 row deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 2814812630

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |          |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | EMP_TEST |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP_TEST |     1 |     7 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=112)


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
          7  consistent gets
          0  physical reads
        316  redo size
        835  bytes sent via SQL*Net to client
        785  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.