Truncate and Delete concept

Hi,
Let us take a scenario such that I issue a delete on table emp and truncate on table emp1 which is exact replica (in terms of both structure and data) of emp table.Now , I issue a commit on both sessions.
If I do a select * from emp and select * from emp1 now, which one should execute faster and why?

Questions by NehaBindaas

Showing Answers 1 - 19 of 19 Answers

Goldest

  • Feb 20th, 2012
 

Delete would leave "log" statements about every deleted thing and truncate wont. Hence truncate would be faster.

  Was this answer useful?  Yes

sampra

  • Mar 6th, 2012
 

Delete would leave "log" statements about every deleted thing and truncate wont. Hence truncate would be faster.

  Was this answer useful?  Yes

Aarti helkar

  • Apr 9th, 2012
 

Detelet is basically used to remove the data from db or from table
but truncate is used to remove data with structure of table.

  Was this answer useful?  Yes

Ravinder Lathwal

  • May 4th, 2012
 

1.We can filter data in delete but in truncate we cant
2.Truncate faster than delete;
3.We can rollback delete but truncate is auto commit.
4.Delete is DML but truncate is DDL.

  Was this answer useful?  Yes

Johny

  • May 18th, 2012
 

Truncate will not delete the structure of the table. That retains the table, but delete every row. The "DROP" command, will delete everything including the structure

  Was this answer useful?  Yes

Waseem Mehmood

  • Sep 4th, 2012
 

- Truncate faster than delete;
- Truncate apply of whole table but one can filter the records in delete command
- We can rollback delete but truncate is auto commit, Once the data has been truncated the data could not be recovered
- Delete is DML but truncate is DDL.

  Was this answer useful?  Yes

Ankur

  • Jan 1st, 2014
 

truncate is faster than delete because truncate is not delete data only deal locate data and is exists still shrink the space ya overwrite the data.

  Was this answer useful?  Yes

rahulnanda

  • Jun 7th, 2014
 

truncate is permanently remove from database so once u commit after truncate the data.u cant rollback.but even after deleting the data using delete command.even after commit we can get the data

  Was this answer useful?  Yes

kranthiswaroop

  • Jun 16th, 2014
 

Hi Neha,

According to me, As Commit is executed on both session though for truncate its not required. Current no data present in table, so both will take same time while executing select stmt.

  Was this answer useful?  Yes

Jaison

  • Jan 17th, 2015
 

Truncate is DDL where as Delete is DML. You cannot use where clause in Truncate and cannot retrieve data once applied. Delete can use where clause and roll back data if Commit is not used. Delete is preferred as developer have clarity that which data have to delete. Truncate clears all data which may be needed for others.
Internally Delete statement will not clears row nums even if record is deleted which will cause performance issue when deleted data is too much. Truncate clears row num too.

  Was this answer useful?  Yes

Ankit Tyagi

  • Aug 30th, 2015
 

1. Delete is DML command where Truncate is DDL Command

2. We can put condition(where clause) in delete but we can not do this truncate

3. If we fire both command data will be deleted but structure will be remain same.

4. In case of delete if we fire command and table occupy some memory it will be remain same but in case of truncate it will clear all memory and allot initial minimum memory.

5. Because truncate is DDL and autocommit and delete is DML

  Was this answer useful?  Yes

Qamar Tahir

  • Sep 20th, 2015
 

TRUNCATE is instantaneous where as DELETE will take a longer time execute

  Was this answer useful?  Yes

Anmol Kaushik

  • Oct 23rd, 2015
 

Each would run without any difference since you have issued a commit.

  Was this answer useful?  Yes

Sajit

  • Oct 31st, 2015
 

Both statements would be processed equally fast as a COMMIT has already been placed after the DELETE and has already reached the next watermark level same as TRUNCATE.

  Was this answer useful?  Yes

Rishab

  • Nov 3rd, 2015
 

Ok, so both tables are now empty and contain no rows.
But the high watermark was decreased for emp1 (as truncate was ran on it).

When Oracle runs a query it checks the high watermark (HWM).
Now a HWM is the max extent till which data was stored in table, this decreases only when one applies a DDL (truncate or drop) and not a DML (delete). So emp1 will be faster.

  Was this answer useful?  Yes

Devendra Shinde

  • Nov 27th, 2015
 

In Truncate we cant use WHERE clause but in DELETE statement we can use WHERE clause. Truncate is faster than delete.

  Was this answer useful?  Yes

Mohammad Mansoor

  • Feb 24th, 2016
 

Truncate and Drop commands Rollback is not possible for Table, whereas Delete Command we rollback the Data

  Was this answer useful?  Yes

sumanamara2016

  • Mar 18th, 2016
 

TRUNCATE
TRUNCATE does not return number of deleted rows.
TRUNCATE TABLE always locks the table and page but not each row.
DELETE
When we have to use DELETE Command then the data deleted can be retrieved when you ROLLBACK .
DELETE statement is executed using a row lock, each row in the table is locked for deletion.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions