Home » SQL & PL/SQL » SQL & PL/SQL » What is Difference between Truncate and delete  () 1 Vote
What is Difference between Truncate and delete [message #197479] Wed, 11 October 2006 07:22 Go to next message
dhanjit
Messages: 6
Registered: October 2006
Location: New Delhi
Junior Member
I know
Truncate delete all rows from a table, cannot rollback
With delete conditional delete is possible and can be rollback

in this answer I have a doubt that I am Defining below

Condition 1 (I am using two ststement)

Delete from emp;

Commit;


Condition 2

truncate table emp;

commit;


in this situation there is no difference in between TRUNCATE & DELETE

then what is use of TRUNCATE

Simply if i have to delete all the record from a table and no need of rollback then which is useful TRUNCATE or DELETE

Thanks


Re: What is Difference between Truncate and delete [message #197486 is a reply to message #197479] Wed, 11 October 2006 07:51 Go to previous messageGo to next message
oradev91
Messages: 3
Registered: September 2006
Junior Member


http://free-advisory.com/forums/oracle/YaBB.pl?num=1160574088/0#1
Re: What is Difference between Truncate and delete [message #197489 is a reply to message #197486] Wed, 11 October 2006 07:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hit the http://www.orafaq.com/forum/fa/456/0/ button and find out:

link
link

And there's more where that came from Wink

This one's closed.

http://www.orafaq.com/forum/fa/448/0/

MHE
Re: What is Difference between Truncate and delete [message #197490 is a reply to message #197479] Wed, 11 October 2006 07:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's a lot of difference between DELETE and TRUNCATE in this case.

1) TRUNCATE will run faster.
2) TRUNCATE will free up all the space used by the table and reset the high watermark. DELETE will keep the space allocated to the table.
3) If you've got your constraints set to cascade, DELETE will delete all the child tables. Truncate will simply fail if there are active FK constraints pointing to the table.
Re: What is Difference between Truncate and delete [message #197504 is a reply to message #197490] Wed, 11 October 2006 08:54 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hey! I closed this one. Trying to prove yourself? Very Happy

MHE

[Updated on: Wed, 11 October 2006 08:54]

Report message to a moderator

Previous Topic: Filtering Data
Next Topic: dblink to sql server returns double recordset
Goto Forum:
  


Current Time: Thu Dec 08 03:53:12 CST 2016

Total time taken to generate the page: 0.07702 seconds