Re: Delete vs. truncate

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 - 16:11:00 CDT