GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Interview Questions  >  Oracle  >  SQL Plus
Go To First  |  Previous Question  |  Next Question 
 SQL Plus  |  Question 3 of 132    Print  
What is difference between TRUNCATE & DELETE

Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. 
DELETE is a DML command and can be rolled back. 
 
Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.



  
Total Answers and Comments: 27 Last Update: June 05, 2008   
  
 Sponsored Links

 
 Best Rated Answer
Submitted by: Deshbir Singh
 
TRUNCATE : You can't use WHERE clause 
DELETE : You can use WHERE clause

Above answer was rated as good by the following members:
bhatnagar_neeraj
  Sorting Options  
  Page 1 of 3   « First    1    2    3    >     Last »  
May 25, 2005 18:30:04   #1  
Scott        

RE: What is difference between TRUNCATE & DELETE
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table) but TRUNCATE is much faster.

 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
June 16, 2005 00:28:26   #2  
Deshbir Singh        

RE: What is difference between TRUNCATE & DELETE
TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
August 19, 2005 10:40:18   #3  
rajiv        

RE: What is difference between TRUNCATE & DELETE
Hi some more
truncate delete+commit -so we cant roll back
delete delete- so it can be rolled back

 
Is this answer useful? Yes | No
August 22, 2005 07:08:11   #4  
Jagdish        

RE: What is difference between TRUNCATE & DELETE
Delete - delete deletes the records from table it can be rollbacked also you can give the where condiition to it.
Truncate - delete all records from table There is no rollback it always commit without givening the commit

 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
September 07, 2005 10:00:24   #5  
Luisa G. Reina        

RE: What is difference between TRUNCATE & DELETE
Truncate: Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent.
Delete: You can keep object's statistics and all allocated space.

 
Is this answer useful? Yes | NoAnswer is useful 1   Answer is not useful 0Overall Rating: +1    
October 14, 2005 06:50:57   #6  
koteswar rao        

RE: What is difference between TRUNCATE & DELETE
Hiwhat is the difference between truncate and delete
 
Is this answer useful? Yes | No
October 15, 2005 04:54:27   #7  
annathurai        

RE: What is difference between TRUNCATE & DELETE

TRUNCATE is a DDL command and cannot be rolled back and All of the memory space is released back to the server. It can not use the Where conditions. DELETE is a DML command and can be rolled back. here can be use where conditions. TRUNCATE is much faster.


 
Is this answer useful? Yes | No
November 10, 2005 03:33:36   #8  
santapan Member Since: October 2005   Contribution: 1    

RE: What is difference between TRUNCATE & DELETE
truncate is a Transcation control language & is run under set auto commit true status.Where as delete is a DML operation where we can easily rolled back the query output.
 
Is this answer useful? Yes | No
November 21, 2005 04:56:46   #9  
Prangya Sahu        

What are the differences between TRUNCATE & DELETE

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.Thatswhy 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.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't 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 cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.


 
Is this answer useful? Yes | No
December 27, 2005 04:10:23   #10  
Deepika S. Verma        

RE: What is difference between TRUNCATE & DELETE

The Main Difference Between DELETE & TRUNCATE Are :-

[1] DELETE - is a DML Command & TRUNCATE - is a DDL Command

[2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records

[3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions

[4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately


 
Is this answer useful? Yes | NoAnswer is useful 0   Answer is not useful 1Overall Rating: -1    
  Page 1 of 3   « First    1    2    3    >     Last »  


 
Go To Top


 Sponsored Links

 
About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape