OraFAQ Forum: Server Administration » Does Delete save space?

Home » RDBMS Server » Server Administration » Does Delete save space?

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

Does Delete save space? [message #118281]

Wed, 04 May 2005 02:12

IA
Messages: 91
Registered: March 2004

Member

Hi Everyone,

I have a very large table, approximately 60 million records.

If I use the DML delete and remove 50 million records, should the size of the segment shrink?

ie, does delete reduce segment size, or do I have to perform a truncate?

Thanks in advance
IA

Report message to a moderator

Re: Does Delete save space? [message #118289 is a reply to message #118281]

Wed, 04 May 2005 02:57

srix5
Messages: 11
Registered: March 2005
Location: Bangalore

Junior Member

Hi IA,

Delete is much costlier than truncate. Delete will generate much undo and slows down the performance also.

If you don't want all the 60 million records, it is better to truncate the table. Truncate will simply resets a high-water mark. Truncate does not use redo and no triggers are fired if there are any for before/after delete. So truncation is better if you do not need all those records.

- SK.

Report message to a moderator

Re: Does Delete save space? [message #118299 is a reply to message #118289]

Wed, 04 May 2005 03:44

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi,

srix5 is right. Unfortunately truncate does not carry conditions. If you want to use delete itself then you have to manage your undo properly as srix5 said. also it depends on the index.

Note: But everything depends on the frequency of deletion and resource usage.

RBN

Report message to a moderator

Re: Does Delete save space? [message #118339 is a reply to message #118281]

Wed, 04 May 2005 09:15

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Deleting 50 million rows is going to take quite a while. With this many rows, why aren't you using partitioning?

Sure truncate is great and wonderful and fast. But if you have 60 million rows and you want to delete 50 million and keep 10 million then truncate alone isn't going to cut it.

You probably want to consider creating a new table and just inserting the 10 million rows that you want to keep into that new table, and then dropping the old table rather than deleting.

Report message to a moderator

Re: Does Delete save space? [message #118396 is a reply to message #118281]

Wed, 04 May 2005 19:47

IA
Messages: 91
Registered: March 2004

Member

Hi Everyone,

Thanks again for all your replies.

But still my question is: does deletion of records save space?

Thanks
IA

Report message to a moderator

Re: Does Delete save space? [message #118399 is a reply to message #118396]

Wed, 04 May 2005 20:25

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

Senior Member
Account Moderator

depends what you call 'deletion'.

-- NO space is released. Highwater mark NOT RESET.
sql> delete from table_name;
-- space is released. Highwater Mark reset.
sql> truncate table table_name;

Please have a look here.
http://www.orafaq.com/forum/m/110688/42800/?srch=emp_loopinsert#msg_110688

[Updated on: Wed, 04 May 2005 22:07]

Report message to a moderator

Re: Does Delete save space? [message #118532 is a reply to message #118281]

Thu, 05 May 2005 12:52

Frank
Messages: 7901
Registered: March 2000

Senior Member

In addition to Mahesh's reply: The deletion does indeed not free up space, but if you will insert new records, the previously occupied space will be re-used (if you do not insert append, that is)

hth

Report message to a moderator

Previous Topic:	UNDO Tablespace and ORA-01555
Next Topic:	Packages Invalid

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 03:39:25 CST 2025