INSERT vs DELETE Execution Plan: Why INSERT is faster than DELETE? (Basic 1 - 20)

 

INSERT and DELETE are two of the most common DML operations in Oracle SQL. INSERT is used to insert new rows into a table, while DELETE is used to delete existing rows from a table.

Why is INSERT faster than DELETE?

INSERT is generally faster than DELETE because it does not need to search for the rows to be deleted. When you insert a new row, Oracle simply places the new row at the end of the table. However, when you delete a row, Oracle needs to search for the row to be deleted. This search can be expensive, especially if the table is large or has many indexes.

Checking the execution plan for INSERT and DELETE

You can use the EXPLAIN PLAN command to see the execution plan for INSERT and DELETE statements. The execution plan shows the steps that Oracle will take to execute the statement.

To see the execution plan for an INSERT statement, set the AUTOTRACE parameter to ON and then execute the INSERT statement. For example, the following code shows how to set AUTOTRACE to ON and then insert a new row into the EMP_TEST table:

SQL

SET AUTOTRACE ON;
INSERT INTO EMP_TEST VALUES (112, 'New', 200450, 'Active', 40, 103);

The following output shows the execution plan for the INSERT statement:

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

To see the execution plan for a DELETE statement, simply execute the DELETE statement. For example, the following code shows how to delete the row that was inserted in the previous example:

SQL

DELETE FROM EMP_TEST WHERE ID = 112;

The following output shows the execution plan for the DELETE statement:

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)

Conclusion

INSERT is generally faster than DELETE because it does not need to search for the rows to be deleted. However, if the table has indexes and the WHERE clause uses the indexed column, then DELETE may be faster than INSERT