Page 1 of 1
Difference between Clear and Truncate
Posted: Wed Jun 16, 2004 6:48 am
by besco
I'd just seen In ORAOCI8, in update action, two choices :
- clear table then insert rows
- truncate table then insert rows
What are differences ?
Posted: Wed Jun 16, 2004 7:06 am
by chulett
'Truncate' means truncate. The table is logically cleared almost instantly.
'Clear' means delete all records. This is transactional and can take time, rollback, etc etc.
Posted: Wed Jun 16, 2004 7:14 am
by besco
Ok, thanks
Re: Difference between Clear and Truncate
Posted: Wed Jun 16, 2004 7:15 am
by acb
besco wrote:I'd just seen In ORAOCI8, in update action, two choices :
- clear table then insert rows
- truncate table then insert rows
What are differences ?
Besco,
Both the Update actions' functionality is the same.
But, the former action deletes the contents of the table and adds
the new rows, with slower performance because of transaction
logging. Whereas, the later truncates the table without transaction logging and hence faster performance.
P.S. Refer Datastage's help.
Regards,
Bhanu.
Posted: Fri Jun 18, 2004 6:00 am
by richdhan
Hi,
I just want to add to Bhanu's comments.
Clear/Delete: For every delete an entry is made in the transaction log.
Advantage:You have the posibility of rollback
Disadvantage:Slower compared to Truncate since it has to make an entry in the transaction log
Truncate: No entry is made in the transaction log.
Advantage:Faster since no entry is made in the transaction log
Disadvantage:You cannot rollback since it is a Data Definition command.
HTH
Rich
Posted: Fri Jun 18, 2004 6:49 am
by AGStafford
To add to Rich's comment.
Since truncate is a DDL command, more authority is required. In our shop we have no DDL authority
and thus must go through a Proc to perform the truncate.
Andrew