When they encounter
errors during this process, they complain that the rollback segments are too small, when in fact
their transaction is too large.
A second problem occurs once the rows have all been deleted. Even though the segment no
longer has any rows in it, it still maintains all the space that was allocated to it. Therefore, deleting
all those rows saved not a single byte of allocated space.
The truncate command resolves both of these problems. It is a DDL command, not a DML
command, so it cannot be rolled back. Once you have used the truncate command on a table,
its rows are gone, and none of its delete triggers are executed in the process. However, the table
retains all its dependent objects??”such as grants, indexes, and constraints.
The truncate command is the fastest way to delete large volumes of data. Because it will delete
all the rows in a table, this may force you to alter your application design so that no protected
rows are stored in the same table as the rows to be deleted. If you use partitions, you can truncate
one partition of a table without affecting the rest of the table??™s partitions (see Chapter 16).
A sample truncate command for a table is shown here:
truncate table EMPLOYEE drop storage;
?–
?–
?–
266 Oracle Database 11g DBA Handbook
This example, in which the EMPLOYEE table??™s rows are deleted, shows a powerful feature of
truncate. The drop storage clause is used to deallocate the non-initial space from the table (this
is the default option).
Pages:
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452