Therefore, you can delete all of a table??™s rows and reclaim all but its initial
extent??™s allocated space, without dropping the table.
The truncate command also works for clusters. In this example, the reuse storage option is
used to leave all allocated space empty within the segment that acquired it:
truncate cluster EMP_DEPT reuse storage;
When this example command is executed, all the rows in the EMP_DEPT cluster will be instantly
deleted.
To truncate a partition, you need to know its name. In the following example, the partition
named PART3 of the EMPLOYEE table is truncated via the alter table command:
alter table EMPLOYEE
truncate partition PART3
drop storage;
The rest of the partitions of the EMPLOYEE table will be unaffected by the truncation of the PART3
partition. See Chapter 16 for details on creating and managing partitions.
As an alternative, you can create a PL/SQL program that uses dynamic SQL to divide a large
delete operation into multiple smaller transactions.
Using Partitions
You can use partitions to isolate data physically. For example, you can store each month??™s
transactions in a separate partition of an ORDERS table. If you perform a bulk data load or
deletion on the table, you can customize the partitions to tune the data manipulation operation.
For example:
You can truncate a partition and its indexes without affecting the rest of the table.
You can drop a partition, via the drop partition clause of the alter table command.
Pages:
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453