Oracle

In Oracle, the TRUNCATE statement is used to remove all rows from a table, but it does so more efficiently than the DELETE statement. It is a Data Manipulation Language (DML) operation that deallocates the data pages, which means it’s faster and uses fewer system resources. Unlike DELETE, TRUNCATE does not generate individual row delete transactions, making it a suitable choice when you need to clear a table quickly.

Here are the key points to understand about TRUNCATE:

1. Purpose and Usage

   • TRUNCATE removes all rows from a table.
   • It is faster and less resource-intensive compared to DELETE because it does not log individual row deletions.

2. Syntax

   • Basic Syntax:

     TRUNCATE TABLE table_name;
     

   • Example:

     TRUNCATE TABLE books;
     

3. Characteristics

   • Speed: Executes faster than DELETE due to minimal logging.
   • Rollback: Cannot be rolled back if executed outside a transaction block.
   • Constraints: Does not activate any triggers associated with the table.
   • Space: Releases space back to the tablespace, which may require a COMMIT to finalize.

4. Comparison with DELETE

   • TRUNCATE is not a DML operation like DELETE but a DDL operation in terms of implementation.
   • DELETE can remove specific rows and can be rolled back if within a transaction.

5. Example with Library Table

   • To truncate a table called books:

     TRUNCATE TABLE books;
     

Remember, TRUNCATE is suitable when you want to remove all data quickly and do not need to worry about individual row deletions or triggers. Use it with caution as it is a non-reversible operation once committed.

To gain complete access, login with gmail or outlook, no need of signup. click here