What is the difference between delete, truncate and drop?

TRUNCATE is DDL command. It is used to delete all the rows from table. You can not use where clause with truncate. It also free up the space containing the table. Truncate is executed by taking table lock. You can not retrieve data even after rollback is performed, in case of truncate as it does not keep a copy while deleting the rows. Because of this it is fast.

DELETE is DML command. It is used to delete rows specified by where clause. If where clause is not used it will delete all the rows of table. Structure is maintained of the table. It doesn’t free up the space containing the table. Delete is executed by taking row level lock.
You can rollback the data, in case of delete as maintains a copy of deleted data in rollback space. Because of this delete is slower than truncate.

DROP is also a DDL command. It is used delete data as well as structure of the table. Even after rollback nothing can be retrieved. If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped.

3 Thoughts on “What is the difference between delete, truncate and drop?

  1. mgs 5 keygen on April 24, 2014 at 6:20 pm said:

    Heey very nice site!! Guy .. Excellent .. Superb .. I’ll bookmark your
    web ssite andd take the feeds also? I’m satisfied to search out a lot of useful ihfo right here in the put up,
    we’d like develop more techniques in this regard, thanks for sharing.
    . . . . .

  2. What is the difference between truncate and drop?

    • anonymous on October 7, 2014 at 6:40 pm said:

      TRUNCATE
      TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.

      SQL> TRUNCATE TABLE emp;

      Table truncated.

      SQL> SELECT COUNT(*) FROM emp;

      COUNT(*)
      ———-
      0
      DROP
      The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

      SQL> DROP TABLE emp;

      Table dropped.

      SQL> SELECT * FROM emp;
      SELECT * FROM emp
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist

Leave a Reply to anonymous Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation