One of Exasol’s key features is the low maintenance effort when compared to many other database systems. Automatic Table Reorganization is one example:
Exasol offers CREATE OR REPLACE TABLE and the DELETE_PERCENTAGE is zero after the table is created. The TABLE_ROW_COUNT is maintained automatically after each data manipulation language (DML) – there’s no need to gather statistics.
10% of rows are marked as deleted for this table but not yet removed. Let’s delete another 100,000 rows and see what happens.
Now 20% of rows are marked as deleted. There’s a threshold of 25% deleted rows that triggers an automatic reorganization:
DELETE_PERCENTAGE resets to zero because of the automatic reorganization – I didn’t have to do anything.
By the way, if INSERTS follows the DELETE, the DELETE_PERCENTAGE decreases automatically too as the space used by the rows marked as deleted is used.
Reorganization can be done manually too:
A manual reorganization is possible but usually isn’t necessary. Typically, reorganization is only needed after the Exasol cluster has been enlarged by adding one or more nodes.