Re: Delete vs. truncate

Isn't there some issue about how space gets deallocated or not depending on whether truncate or delete is used?

When you delete or truncate does one of them actually release the space which had been occupied by rows and the other does not? Also, what affect does ALTER TABLE XXX DEALLOCATE UNUSED have after either a truncate or a delete?

The thing that I always must be careful of is that most of my tables have complex foreign key constraints and triggers so I really don't have the option of selecting the rows into a new table unless I can get database quiessence and ensure that I get all of constraints correct afterwards.

Ken

Chris Weiss wrote:
>
> DELETE is good when you have a WHERE clause. However, DELETE uses rollback
> log space, and for large tables, it can easily crush your rollback space if
> you are not careful.
>
> Truncate is used to completely empty a table. Always use TRUNCATE for
> emptying a table.
>
> If you are preserving many fewer records than you are deleting, create a
> copy of the records you wish to save with a "create table as" command,
> truncate the original table, and then add the other records back.
>
> The biggest difference is that DELETE is a transaction, ie. you can roll it
> back prior to a commit. However, transactions can consume large number of
> resources per record. TRUNCATE is a DBMS action which removes all the
> records in a table and it cannot be directly undone.
>
> Truncate is always faster than DELETE.
>
> Hope this answers your questions.
>
> Christopher Weiss
> Compuware Corporation
> Professional Services Division
>
> <shuchi_at_my-deja.com> wrote in message news:7kos5p$jh3$1_at_nnrp1.deja.com...
> > My question is general one regarding purging of data from the database.
> > Are there situations when delete from table is better then truncate
> > table? IF so when.
> > Which is faster of the two?
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
Received on Tue Jun 22 1999 - 20:05:49 CDT