Difference between Clear and Truncate

[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 ?

[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.

[besco]

Ok, thanks

[acb]

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.

[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

[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