OraFAQ Forum: SQL & PL/SQL » diff between TRUNCATE & DELETE

Home » SQL & PL/SQL » SQL & PL/SQL » diff between TRUNCATE & DELETE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

diff between TRUNCATE & DELETE [message #7563]

Sun, 22 June 2003 01:45

shashi
Messages: 34
Registered: March 2001

Member

TRUNCATE AND DELETE do the same job except that TRUNCATE as an implicit COMMIT.
So why to have 2 commands doing the same job.
Is there anything else to differentiate them

Report message to a moderator

Re: diff between TRUNCATE & DELETE [message #7565 is a reply to message #7563]

Sun, 22 June 2003 08:52

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

that makes the BIG difference.
truncate does the implicit commit and resets the highwater mark.
in simple , say u have a table with 10 Megs worth of records.
you delete 5 megs worth of records.
But the table still occupies 10 megs.
But if u truncate the table it becomes 0 meg ( becuase with delete u can use a where clause and with truncate u cannot use a where clause)

Report message to a moderator

Re: diff between TRUNCATE & DELETE [message #7582 is a reply to message #7565]

Mon, 23 June 2003 11:05

shashirekhabk@rediffmail.
Messages: 1
Registered: June 2003

Junior Member

Thanx....
so a WHERE clause is another factor which differentiates them

Report message to a moderator

Re: diff between TRUNCATE & DELETE [message #7616 is a reply to message #7563]

Wed, 25 June 2003 04:55

Chandru
Messages: 12
Registered: July 2000

Junior Member

Hi,

Here are the differences between the two

1. Delete allows you to filter the records that have to be deleted, while with Truncate you will have to delete all the records only.

2. Since Delete doesnot use an implicit commit, deleted rows can be rolled back, while truncate cannot be rolled back. This is why Deleting all records from a large table is much slower than Truncating the table, because when you delete all the rows, they are written into the RBS and stay there till you do a commit or a rollback.

3. Even if you use Cascade Delete referntial integrity, Truncate will not allow you to delete parent records unless the constarint is disabled. On the other hand Delete will proceed under such circumstances.

4. Unlike what Mahesh said, Truncate also has the option to leave the storage to be reused. But the default option is Drop Storage.

HTH
Chandru

Report message to a moderator

Re: diff between TRUNCATE & DELETE [message #7661 is a reply to message #7616]

Fri, 27 June 2003 10:54

shashi
Messages: 34
Registered: March 2001

Member

Thanks for clarification

Report message to a moderator

Previous Topic:	deleting,inserting,updating multiple tables
Next Topic:	ORACLE JOBS PROBLEM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 10 08:19:01 CDT 2024