'Truncating' a Table

The following discussion holds good for V7 ...

1. Using DELETE

DELETE FROM <tablename>

This causes all the rows in the table to be deleted. Log records are written. If the table is large(in terms of millions of records), a very large active log space is required. DELETE Triggers are fired.

2. Using IMPORT/LOAD

IMPORT from /dev/null of del
replace into <tablename>
LOAD from /dev/null of del
replace into <tablename> NONRECOVERABLE

More privelages are required for these tasks. IMPORT .. REPLACE requires CONTROL on the table and LOAD requires LOAD Authority on the table. Good thing about this is that there is minimal logging.
The tables may be left in check pending state. DELETE Triggers are not fired.

From the database recovery point of view, it is advisable to use IMPORT

3. Using NLI

ALTER TABLE <tablename> NOT LOGGED INITIALLY WITH EMPLY TABLE

The table should have been created with the NLI Option. No logging is done. DELETE triggers are not fired.