REORGANIZE ENFORCE may not remove rows marked as deleted

Detail name	Value
Changelog Number	13340
Type	Bug
Status	Open
Affected Versions	Exasol 6.2.15

Background

For efficiency reasons, when deleting rows from a table via DELETE or MERGE statements, the rows in the table are not actually deleted, but only marked as deleted; the data contained in them is still there but is never used. Only when a certain threshold is reached (default 25% of the total number of rows) are these marked rows actually deleted; we say that the table is reorganized. This reorganization can also be triggered explicitly using the following command:

  REORGANIZE TABLE <table> ENFORCE;

Note: the ENFORCE clause is optional and ensures the reorganization is always carried out. In its absence, if the number of deleted marked rows is less than half the threshold (default 12.5%) then no reorganization is carried out.

Description

In very rare cases, a REORGANIZE TABLE ... ENFORCE statement may not remove the rows marked as deleted.

Workaround

If possible, recreate the table by inserting the data into a new, temporary table and renaming the table. NOTE: Object privileges will be lost after dropping the old table:

CREATE TABLE <tmp-name> LIKE <target-table>;
INSERT INTO <tmp-name> SELECT * FROM <target-table>;
DROP TABLE <target-table>;
RENAME <tmp-name> TO <target-table>

Fix

In this very rare case, REORGANIZE ENFORCE will remove rows marked as deleted, as expected.