TRUNCATE DROP AND DELETE SQL Statement

TRUNCATE SQL Statement

TRUNCATE is a DDL command. It performs the same function as a DELETE statement without a WHERE clause. TRUNCATE is executed using a table lock and whole table is locked for remove all records. This statement mostly used to delete the table data permanently. Truncate statement deletes the storage occupied by the table. The deleted storage can be used by the table in future. We cannot use Where clause with TRUNCATE. The truncate statement can’t be rolled back also we can’t fire any trigger. To truncate a table, the table must be in our schema or we must have DROP ANY TABLE system privilege. Truncate statement is faster and does not use undo as delete statement. In truncate there is minimal logging in transaction log, so it is performance wise faster. De-allocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. Also Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process. To use Truncate on a table you need at least ALTER permission on the table. Truncate uses the less transaction space than Delete statement. But truncate cannot be used with indexed views.

Syntax

TRUNCATE TABLE TABLE_NAME;

Example

Let us have a look at below STUDENTS_COPY table.

SELECT * FROM STUDENTS_COPY;

TRUNCATE TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Now the all the records of STUDENTS_COPY table are removed, but table structure is present.

Preserving Materialized View Logs After Truncate

TRUNCATE TABLE TABLE_NAME PRESERVE MATERIALIZED VIEW LOG;

DELETE SQL Statement

DELETE is a DML command. DELETE is executed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete specific records. It maintains the log, so it is slower than TRUNCATE. Delete uses the more transaction space than TRUNCATE statement. To use Delete you need DELETE permission on the table. It can be used with indexed views.

Syntax

DELETE FROM TABLE_NAME

[WHERE CONDITION];

Have a look at our previous post on DELETE SQL query. TRUNCATE is DDL statement which implicitly commits. If you are not sure of the action you do in a schema, and want to commit only after a manual verification, then do not TRUNCATE, use DELETE instead.

DROP SQL Statement

DROP is a DDL command. The DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints, permission specifications for that table and indexes of related objects.Drop statement is an irreversible statement. the operation cannot be rolled back. When we execute the DROP statement then no DML triggers will be fired.

Syntax

DROP TABLE TABLE_NAME;

Example

Let us DROP STUDENTS_COPY by using DROP statement.

DROP TABLE STUDENTS_COPY;

SELECT * FROM STUDENTS_COPY;

Table does not exist after drop statement.