sangeethaso

 Profile Answers by sangeethaso 

• Aug 26th, 2010

 

Truncate and Drop are DDL commands.

Truncate table will remove all the rows and datas in a table but table structure is not removed.
whereas in drop table the entire table is removed and moved to recycle bin.


Truncate is mostly useful when you need to remove the existing rows and recreate a new table with same structure, since the table structure, indexes, constraints, triggers and privileges are already existing and it saves time.

Truncate is faster compared to Drop.

ram26raju

 Profile Answers by ram26raju 

• Sep 4th, 2010

 

Truncate table will remove all the rows and data in a table but table structure is not removed whereas in drop table the entire table is removed and moved to recycle bin.

Radha

• Jul 13th, 2011

 

Delete is DML, where as Truncate is DDL.
We can get back the rows which is deleted by using flash back but we can get back the Truncated rows.
We can have where clause in Delete but we can't have where clause in Truncate. Truncate is faster than Delete

Mohan Suresh R

• Jul 13th, 2011

 

Since TRUNCATE is a DDL command, it cannot be rolled back. so it need not store the table values into the REDO buffer. Whereas DELETE is a DML command and it can be rolled back. so DELETE command have to store all data into REDO buffer before deleting it from table. it will take some time to copy data into REDO buffer. So, obviously TRUNCATE is much faster than DELETE.

Bulti

• Apr 2nd, 2015

 

Question is Drop vs truncate then why you have mention truncate vs delete..delete is diff from drop.Delete is DML whereas Drop is DDl. The main diff is DROP delete data as well as table structure where truncate deletes data only. Both are auto commit

Nupur

• Apr 24th, 2015

 

Truncate will just remove the data but structure is there but in drop the entire structure is gone . 
No idea on which one is faster, but think that truncate is faster when compared 

ALOK SRIVASTAVA

• Jun 1st, 2015

 

TRUNCATE is faster than DROP because DROP does double work - First it removes data and secondly it removes the structure of table where as truncate does only one work - it just deletes the data.

Reshma

• Jun 3rd, 2015

 

Drop command deletes the table structure completely, and ultimately the table will no more be available in the database. However, truncate command deletes the data in two steps: First it deletes the table structure and then recreate it. So in truncate all data gets deleted but table structure remains in the database.

The delete command also does the same job as truncate but it is slower because it deletes the data one by one.

Monty Malhotra

• Jul 8th, 2015

 

Truncate deletes the Data in the table as well as the transcation Log for the same. Its two step process, Here table structure remain in the database.
Drop will delete the whole structure of table i.e table not exist in the database.
Drop is faster then truncate.

subbarao

• Nov 6th, 2015

 

I have one doubt which is one is faster some people said comp with drop truncate very faster but some one drop very faster comp with truncate.

anjaneulu

• Nov 26th, 2015

 

TRUNCATE means it deletes the table data but the table structure remains as it is. Whereas in DROP command it deletes the table data as well as its structure also.

VK

• Feb 18th, 2016

 

Truncate is faster as it just resets the high water mark where as delete is a logged operation.

Ravi Teja

• Feb 23rd, 2016

 

Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

Reason:
When you type DELETE. All the data get copied into the Rollback Tablespace first. Then delete operation get performed. That is why when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Tablespace). All this process take time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That is why TRUNCATE is faster. Once you Truncate you cannot get back the data.

Removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters. Truncating has none of these effects.

JAVED KHAN

• Apr 6th, 2016

 

Yes, the two answers given is correct about High Water Mark and Constraints but moreover I agree with the fact VK said "Its a logged Operation". So while Oracle deletes the records its getting written in undo tablespace for you to recall in case you want to rollback basically its creating reverse scripts for you. Also each and every record will be checked for dependencies and constraints, Truncate will not check constraints also wont prepare rollback scripts: that is the reason once truncated you wont get the things back. For high water mark I am actually little sour : it wont always reset your high water mark as I saw in my career so far.

Ashish

• May 2nd, 2016

 

1. Drop means delete database object with schema and data, but in truncate we only delete data and reset identity property.

2. We can apply drop on all database object, but we cannot apply truncate to all database object just like we cannot truncate index but we drop index.

Rajan

• Mar 17th, 2017

 

1. TRUNCATE is a DDL command whereas DELETE is a DML command.

2. TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first, then delete operation get performed. That is why when you type ROLLBACK after deleting a table ,you can get back the data (The system get it for you from the Rollback Tablespace). All this process take time. But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace. That is why TRUNCATE is faster. Once you Truncate you cannot get back the data.

3. You cannot rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4. In case of TRUNCATE, Trigger doesn't get fired.But in DML commands like DELETE. Trigger get fired.

5. You cannt use conditions (WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause.

masthan

• May 30th, 2017

 

Truncate is faster compared to Drop