Deleting Rows From a Column-Partitioned Table - Teradata Database

Deleting Rows From a Column‑Partitioned Table

Consider the following information before deleting data from a column‑partitioned table.

A DELETE ALL request or an unconstrained DELETE request takes the fastpath DELETE for any table. If specified within an explicit transaction in Teradata session mode, the DELETE request must be the last statement of the last request of the transaction.

Similarly, if the column‑partitioned table is also row‑partitioned, Teradata Database can do a fastpath DELETE. For these cases, Teradata Database recovers the space that had been used by the deleted rows.

A fastpath optimization is one that can be performed faster if certain conditions are met. For example, in some circumstances DELETE and INSERT operations can be performed faster if they can avoid reading the data blocks and avoid transient journaling.

For all other cases, a DELETE request uses a scan or an index on a column‑partitioned table. In this case, the rows are marked as deleted in the delete column partition without recovering the space, but both LOB space and index space is recovered. If column partitions with ROW format do have their space deleted and if all column partitions have ROW format, the row is not marked as deleted in the delete column partition.

Because of this, you should only delete rows in this manner for a relatively small number of rows and you should use the form of DELETE request described in the previous bullet to delete large amounts of rows.

The space is recovered from the column‑partitioned table when all the rows are deleted at the end of a transaction or when the entire row partition that contains the deleted rows is deleted at the end of a transaction.