Difference between drop table and truncate table?

Question

I have some tables that I build as a part of my report rollup. I don't need them afterwards at all. Someone mentioned to truncate them as it would be faster.

Answer 1

Delete Statement

Delete Statement delete table rows and return the number of rows is deleted from the table.in this statement, we use where clause to deleted data from the table

• Delete Statement is slower than Truncate statement because it deleted records one by one

Truncate Statement

Truncate statement Deleted or removing all the rows from the table.

• It is faster than the Delete Statement because it deleted all the records from the table
• Truncate statement not return the no of rows are deleted from the table

Drop statement

Drop statement deleted all records as well as the structure of the table

Answer 2

DELETE

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

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.

From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Answer 3

The answers here match up to the question, but I'm going to answer the question you didn't ask. "Should I use truncate or delete?" If you are removing all rows from a table, you'll typically want to truncate, since it's much much faster. Why is it much faster? At least in the case of Oracle, it resets the high water mark. This is basically a dereferencing of the data and allows the db to reuse it for something else.

Answer 4

Drop gets rid of the table completely, removing the definition as well. Truncate empties the table but does not get rid of the definition.

Answer 5

DROP and TRUNC do different things:

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

DROP TABLE

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.

As far as speed is concerned the difference should be small. And anyway if you don't need the table structure at all, certainly use DROP.

Answer 6

Drop drop whole table and all its structure

truncate delete all rows from table it is different from delete that it also delete indexes of rows