Consider the following information before updating data in a column-partitioned table.
- An UPDATE request uses a scan, an index, or a rowID spool to access a column-partitioned table and select the qualifying rows for the update.
- An UPDATE request is processed in the following way:
- Selects the rows to be updated.
- Transforms columns to rows.
- Deletes the old row without recovering the space and marks its delete bit in the delete column partition.Both LOB space and index space is recovered.
- Reinserts the updated rows as columns and appends the column values to their corresponding combined partitions.
Database Engine 20 recovers the space from the column-partitioned table when deleting all of the rows at the end of a transaction or when deleting the entire row partition that contains the deleted rows at the end of a transaction.
If the columns being updated are only in column partitions of a table with ROW format (and the columns being updated are not primary AMP index, primary index, or partitioning columns), the update is made in place instead of as a delete of the old row and an insert of the new row.
- Database Engine 20 also updates the columns in the column-partitioned table that are used in a secondary or join index.