Home » SQL & PL/SQL » SQL & PL/SQL » difference between trunc and delete (oracle)
difference between trunc and delete [message #303333] Thu, 28 February 2008 22:59 Go to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Hi Experts,
what is the difference between delete and truncate?

Thanks,
Sheldon
Re: difference between trunc and delete [message #303335 is a reply to message #303333] Thu, 28 February 2008 23:11 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Delete and trunc does the same functionality.If you trunc the table then you cant rollback again but in delete you can rollback.Trunc is auto commit.
Re: difference between trunc and delete [message #303338 is a reply to message #303333] Thu, 28 February 2008 23:17 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
ok..
now if i want to delete large number of rows from the table. which command will be better??
Re: difference between trunc and delete [message #303339 is a reply to message #303333] Thu, 28 February 2008 23:21 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
truncate
- you can't specify a condition.
- you can't rollback what you have deleted.

delete
- you can specify a where clause .
- you can rollback the delete.



regards,
Re: difference between trunc and delete [message #303340 is a reply to message #303333] Thu, 28 February 2008 23:23 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
i will be happy if you will explain me in more detail
Re: difference between trunc and delete [message #303341 is a reply to message #303333] Thu, 28 February 2008 23:24 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
suppose if i want to delete one millions of records from the tables which contains 2 millions?
Re: difference between trunc and delete [message #303342 is a reply to message #303333] Thu, 28 February 2008 23:26 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
delete


regards,


Re: difference between trunc and delete [message #303344 is a reply to message #303333] Thu, 28 February 2008 23:29 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
do i know the reason???
Re: difference between trunc and delete [message #303345 is a reply to message #303344] Thu, 28 February 2008 23:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Contrary to what was claimed earlier in this thread, delete and truncate are TOTALLY different.
Delete removes rows from a table, leaving all space allocated to the table.
Truncate moves the HWM, thus returning the space the table used.

Because of this difference, truncate can not be rolled back (it is a DDL statement, rather than a DML statement). Because the HWM is reset, there is no way to 'partially' truncate a table, so removing one million rows from a two million rows table can not be done by truncating it.
Re: difference between trunc and delete [message #303347 is a reply to message #303342] Thu, 28 February 2008 23:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
just a thought.
1 - create a table containing the 1 million rows.
2 - truncate the table with contains 2 million rows.
3 - insert the records from the table containing 1 million records and then drop the table created in the 1st step.
by truncating you may release the space occupied by the records which you don't want.


regards,
Re: difference between trunc and delete [message #303348 is a reply to message #303333] Thu, 28 February 2008 23:40 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Thanks a lot
Re: difference between trunc and delete [message #303350 is a reply to message #303347] Thu, 28 February 2008 23:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dhananjay wrote on Fri, 29 February 2008 06:37
just a thought.
1 - create a table containing the 1 million rows.
2 - truncate the table with contains 2 million rows.
3 - insert the records from the table containing 1 million records and then drop the table created in the 1st step.
by truncating you may release the space occupied by the records which you don't want.


regards,

Why move the records around?

2 - drop original table
3 - rename new table to old tablename and recreate indexes/keys
Re: difference between trunc and delete [message #303353 is a reply to message #303350] Thu, 28 February 2008 23:53 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
yes ofcourse you can get rid of those extra steps of moving the data.thanks for correcting Frank.


regards,
Re: difference between trunc and delete [message #303459 is a reply to message #303345] Fri, 29 February 2008 07:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Truncate moves the HWM, thus returning the space the table used.

This is true by default because internally this will be executed as
truncate table <table_name> drop storage;

But if you use
 truncate table <table_name> reuse storage;
It will reset the HWM (High water mark) on the mentioned table but it won't return the storage back to the tablespace instead the object will retain the storage.

Regards

Raj
Re: difference between trunc and delete [message #303480 is a reply to message #303459] Fri, 29 February 2008 08:51 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the correction, didn't realise that.

I live a very default life, I guess..
Previous Topic: SQL
Next Topic: Limiting Number of Duplicates?
Goto Forum:
  


Current Time: Sun Dec 04 14:36:45 CST 2016

Total time taken to generate the page: 0.15687 seconds