How to use Exasol’s Automatic Table Reorganization

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.

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.