In Oracle Database, there are three main SQL operations that can be used to remove data from a table: DELETE, TRUNCATE, and DROP. While they all have the same end result of removing data from a table, they differ in their implementation and effects.
1. DELETE: The DELETE operation is used to remove one or more rows from a table. It is a DML (Data Manipulation Language) statement. It can be used with a WHERE clause to specify which rows to delete. For example, the following SQL statement would delete all rows from the Customers table where the Country column equals ’USA’:
DELETE FROM Customers
WHERE Country = 'USA';One important thing to note about the DELETE operation is that it only removes data, it does not free up the space occupied by the data. The space remains allocated to the table, and will be used for new data inserted into the table.
2. TRUNCATE: The TRUNCATE operation is used to remove all rows from a table, effectively resetting the table. It is a DDL (Data Definition Language) statement. Unlike DELETE, it does not require a WHERE clause to specify which rows to delete. For example, the following SQL statement would truncate the Customers table, removing all data from it:
TRUNCATE TABLE Customers;One important thing to note about the TRUNCATE operation is that it removes all data from the table, but it also resets any sequences that were associated with the table, effectively starting them over from their beginning values. Additionally, the space occupied by the data is freed up and returned to the tablespace, making it available for other tables to use.
3. DROP: The DROP operation is used to remove an entire table from the database. It is a DDL statement. For example, the following SQL statement would drop the Customers table, removing it completely from the database:
DROP TABLE Customers;One important thing to note about the DROP operation is that it removes the entire table and all its data, as well as any associated indexes, constraints, and triggers. This operation is not reversible, and any data in the table will be lost permanently. It is important to use caution when using the DROP operation.
In summary, the main differences between the DELETE, TRUNCATE, and DROP operations in Oracle Database are:
- DELETE removes individual rows from a table, while TRUNCATE removes all rows from a table.
- DELETE preserves the table's structure and any associated constraints and triggers, while TRUNCATE resets any sequences associated with the table and frees up the space occupied by the data.
- DROP removes the entire table and all its associated data, indexes, constraints, and triggers permanently.