Truncate and Delete
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
TRUNCATE : You can’t use WHERE clause
DELETE : You can use WHERE clause
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to
COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
TRUNCATE removes all rows from a table. The operation cannot be rolled back. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.
The DROP command removes a table from the database. All the tables’ rows,
indexes and privileges will also be removed. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
DDL – Data Definition Language: statements used to define the database structure or schema. Some examples:
• CREATE – to create objects in the database
• ALTER – alters the structure of the database
• DROP – delete objects from the database
• TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
• COMMENT – add comments to the data dictionary
• RENAME – rename an object
DML – Data Manipulation Language: statements used for managing data within schema objects. Some examples:
• SELECT – retrieve data from the a database
• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – deletes all records from a table, the space for the records remain
• MERGE – UPSERT operation (insert or update)
• CALL – call a PL/SQL or Java subprogram
• EXPLAIN PLAN – explain access path to data
• LOCK TABLE – control concurrency
DCL – Data Control Language. Some examples:
• GRANT – gives user’s access privileges to database
• REVOKE – withdraw access privileges given with the GRANT command
TCL – Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
• COMMIT – save work done
• SAVEPOINT – identify a point in a transaction to which you can later roll back
• ROLLBACK – restore database to original since the last COMMIT
• SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use
What are Stored Procedures?
Stored procedures are set of SQL commands that are stored in the database data server. After the storing of the commands is done, the tasks can be performed or executed continuously, without being repeatedly sent to the server. This also helps in decreasing the traffic in the networks and also reduces the CPU load.
Truncate and Delete