Difference between Delete Truncate and Drop commands

Delete Truncate and Drop -Learn basic differences between Delete Truncate and Drop commands with examples.

DELETE	TRUNCATE	DROP
DML command	DDL command	DDL command
Need explicit commit to delete the data from the table, other wise data will be rolled back	Truncate is a auto commit statement, i.e once you execute the truncate command , data will deleted from the table and cannot be rolled back.	DROP is a auto commit statement. Removes a table from the database and cannot be rolled back
Can use where condition to delete the specific records from the table.	It is not possible to use where in truncate statement. when you execute the truncate command, all rows will be removed from the table.	It is not possible to use where in DROP statement. when you execute the DROP command, Removes a table from the database .
Trigger will get fired when delete command is issued(on delete triggers)	No triggers get will invoked on truncate command	No triggers get will invoked on DROP command
Delete command is slower because, it maintains log for the deleted records and we can undo the deleted records with roll back statement.	Truncate is faster than delete because , it wont maintain any log to undo the delete records
Delete can be used with indexed views.	Truncate cannot be used with indexed views.

To Know more about Delete , Drop and Truncate, Please click on the hyper links.

Example for storage space reduction difference in table by using delete and truncate command

Please execute  below the queries.

Queries related to delete command

CREATE TABLE STUDENT_TABLE(STDID NUMBER, STDUDENT_NAME VARCHAR2(100));

INSERT INTO STUDENT_TABLE(STDID,STDUDENT_NAME)
SELECT LEVEL,'STDUDENT_NAME'||LEVEL FROM DUAL CONNECT BY LEVEL <=1000000;

SELECT segment_name Table_name, ((bytes/1024)/1024) MB FROM dba_segments where segment_name='STUDENT_TABLE' ORDER BY 1 DESC;

Result -1 

Storage space for 1 million records in the table before deletion.

Table_Name	MB
STUDENT_TABLE	33

DELETE FROM STUDENT_TABLE;

Deletion of records Task completed in 15.908 seconds.

SELECT segment_name Table_name, ((bytes/1024)/1024) MB FROM dba_segments where segment_name='STUDENT_TABLE' ORDER BY 1 DESC;

Result -2

Storage space for the table after deleting 1 million records.

Table_Name	MB
STUDENT_TABLE	33

Queries related to Truncate command

CREATE TABLE STUDENT_TABLE(STDID NUMBER, STDUDENT_NAME VARCHAR2(100));

INSERT INTO STUDENT_TABLE(STDID,STDUDENT_NAME)
SELECT LEVEL,'STDUDENT_NAME'||LEVEL FROM DUAL CONNECT BY LEVEL <=100;

SELECT segment_name Table_name, ((bytes/1024)/1024) MB FROM dba_segments where segment_name='STUDENT_TABLE' ORDER BY 1 DESC;

Result -3

Storage space for 1 million records in the table before truncate.

Table_Name	MB
STUDENT_TABLE	33

TRUNCATE TABLE STUDENT_TABLE;

Deletion of records Task completed in 0.616 seconds.

SELECT segment_name Table_name, ((bytes/1024)/1024) MB FROM dba_segments where segment_name='STUDENT_TABLE' ORDER BY 1 DESC;

Result -4

Storage space for table  after truncating 1 million records .

Table_Name	MB
STUDENT_TABLE	0.0625

Points to notice:

1. If we observe the time difference between delete and truncate statements , to delete the records on table using delete command it was taken 15.908 seconds and to the remove records on table using truncate command it was taken 0.616 seconds. With this observation we can say that Truncate is faster than delete command.
2. If we observe the Result -2 and Result -4, storage space will not be reduced if we issue delete command on the table and storage space will be reduced when we issue truncate command on the table.

Example query to fire a trigger on deleting records with delete statement

CREATE OR REPLACE TRIGGER trigger_on_STUDENT_TABLE AFTER
  INSERT OR
  UPDATE OR
  DELETE ON STUDENT_TABLE FOR EACH row 
BEGIN 
 raise_application_error(-20001,'Trigger is fired');
END;

Query to delete records on the table

DELETE FROM STUDENT_TABLE;

Output-1:

Error report:
SQL Error: ORA-20001: Trigger is fired
ORA-06512: at “TRAINING593.TRIGGER_ON_STUDENT_TABLE”, line 2
ORA-04088: error during execution of trigger ‘TRAINING593.TRIGGER_ON_STUDENT_TABLE’

When we issue a delete command on the table, on delete triggers gets fired.

Query to remove records on the table

TRUNCATE TABLE STUDENT_TABLE;

Output-2:

table STUDENT_TABLE truncated.

If we observe the above output-1 and output-2,

1. In output-1 trigger gets fired on the table on issuing a delete command and no records will delete due to raising an exception.
2. In output-2 no trigger fired  and removed records successfully from the table.