Use this statement to reorganize the database internally. This is necessary if the database cluster was enlarged. This statement redistributes the data across the nodes and reconstitutes the distribution and partitioning status (for further details, see ALTER TABLE (distribution/partitioning)).
This statement should be executed immediately after the cluster enlargement. Otherwise, the system performance may have an impact.
You need access to the following:
- All tables by the ownership of the user or any of its roles.
- Any of the modifying ANY TABLE system privileges or any modifying object privilege.
- All modifying privileges except SELECT.
- If you specify the option DATABASE, all existing tables are reorganized.
- Multiple tables are reorganized step-by-step and an implicit COMMIT is executed after each table. This leads to less transaction conflicts and improves the performance when accessing the reorganized tables.
- The reorganization is done through the following actions:
- DELETE reorganization: Refilling rows that are only marked as deleted (a type of defragmentation). This happens automatically after reaching a certain threshold, but can be explicitly triggered with this command. For further details, see DELETE statement.
- Recompressing the columns whose compression ratio decreased significantly over the period.
- Recreation of all internal indexes.
- TABLE redistribution: Distributing the rows evenly across the cluster nodes and re-establishing the DISTRIBUTE BY status after a cluster enlargement.
- If you specify the ENFORCE option, all specified tables are reorganized. Otherwise, only those tables are adjusted where this operation is absolutely necessary (for example, due to a cluster enlargement or lot of rows marked as deleted).
- The execution time of this command can take some time. Each COMMIT writes a lot of data to discs.