Difference between DELETE and TRUNCATE in Oracle

In Oracle, both the DELETE statement and the TRUNCATE statement are used to remove data from a table, but they have some key differences:-

1. DELETE Statement:

   - The DELETE statement removes rows from a table based on specified conditions using a WHERE clause.

   - It is a DML (Data Manipulation Language) operation.

   - DELETE statement can be rolled back using the ROLLBACK statement if used within a transaction.

   - Triggers associated with the table are fired for each deleted row.

   - The DELETE statement can be selective, allowing you to delete specific rows based on certain criteria.

2. TRUNCATE Statement:

   - The TRUNCATE statement removes all rows from a table.

   - It is a DDL (Data Definition Language) operation.

   - TRUNCATE operation cannot be rolled back.

   - Triggers associated with the table are not fired when using TRUNCATE.

   - TRUNCATE operation resets high water mark, deallocating space used by the table, and releases the storage associated with the table, making it a faster operation compared to DELETE, especially for large tables.

   - TRUNCATE does not generate any undo information.

Let's illustrate the difference between the DELETE and TRUNCATE statements in Oracle with an example:-

Suppose we have a table named employees with the following data:-

| id |   name   | age |   city   |

|----|----------|-----|----------|

|  1 | John     |  30 | New York |

|  2 | Emily    |  28 | London   |

|  3 | Michael  |  35 | Paris    |

|  4 | Sophia   |  32 | Tokyo    |

Example 1: Using DELETE Statement

Step 1: Write the DELETE statement to remove rows where the age is greater than 30.

DELETE FROM employees

WHERE age > 30;

Step 2: Execute the DELETE statement.

Step 3: Show the updated table.

| id |   name   | age |   city   |

|----|----------|-----|----------|

|  1 | John     |  30 | New York |

|  2 | Emily    |  28 | London   |

Output: The rows with IDs 3 (Michael) and 4 (Sophia) have been deleted because their age was greater than 30.

Example 2: Using TRUNCATE Statement

Step 1: Write the TRUNCATE statement to remove all rows from the employees table.

TRUNCATE TABLE employees;

Step 2: Execute the TRUNCATE statement.

Step 3: Show the updated table.

| id | name | age | city |

Output: All rows from the employees table have been deleted. The table is now empty.

- The DELETE statement removed specific rows based on a condition (age > 30), leaving the table partially populated.

- The TRUNCATE statement removed all rows from the table, resulting in an empty table.

In summary, the main differences between DELETE and TRUNCATE in Oracle are their granularity (individual row vs. entire table), ability to roll back, triggering of associated triggers, and performance characteristics. DELETE is used for selective row removal with rollback capability, while TRUNCATE is used to quickly remove all rows from a table without rollback capability and without triggering associated triggers.

Here are five frequently asked questions (FAQs) about the DELETE statement and TRUNCATE statement in Oracle:-

1. What is the difference between the DELETE statement and TRUNCATE statement in Oracle?

   - The DELETE statement removes individual rows from a table based on specified conditions, while the TRUNCATE statement removes all rows from a table. Additionally, DELETE is a DML operation that can be rolled back and fires triggers, whereas TRUNCATE is a DDL operation that cannot be rolled back and does not fire triggers.

2. When should I use the DELETE statement over the TRUNCATE statement in Oracle?

   - Use the DELETE statement when you need to selectively remove specific rows from a table based on conditions or when triggers associated with the table need to be fired. Use the TRUNCATE statement when you want to quickly remove all rows from a table and reset the table's storage space, especially for large tables.

3. Can I roll back a TRUNCATE operation in Oracle?

   - No, the TRUNCATE operation cannot be rolled back in Oracle. Once the TRUNCATE statement is executed, the data is permanently removed from the table, and the operation cannot be undone.

4. Do triggers associated with the table fire during a TRUNCATE operation in Oracle?

   - No, triggers associated with the table are not fired when the TRUNCATE statement is executed in Oracle. This is in contrast to the DELETE statement, which fires triggers for each deleted row.

5. Which statement is faster, DELETE or TRUNCATE, in Oracle?

   - Generally, the TRUNCATE statement is faster than the DELETE statement in Oracle, especially for large tables. This is because TRUNCATE does not generate undo information, reset high water mark, and release the storage associated with the table, making it a more efficient operation for bulk data removal.