REORGANIZE

Purpose

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 more details, see ALTER TABLE (Distribution/Partitioning)).

Prerequisites

  • The system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema(s), or the schema(s) must be owned by you or one of your assigned roles.
  • Any of the modifying ANY_TABLE system privileges, or any of the modifying object privileges (ALTER, INSERT, UPDATE, DELETE) on all affected tables, or the tables must be owned by you or one of your assigned roles.
  • RECOMPRESS DATABASE requires that you have modifying rights to all tables in the database.

Syntax

reorganize::=

Reorganize statement

Usage Notes

  • 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.
    • Re-compressing 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.

  • A DELETE reorganization only occurs if at least 12.5% of a table’s rows are marked as deleted unless ENFORCE is specified. The percentage of rows marked as deleted is found in the DELETE_PERCENTAGE column in EXA_ALL_TABLES.

  • 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.
  • If you specify the DATABASE option, all existing tables are reorganized.

  • If you specify the ENFORCE option, all specified tables are reorganized. Otherwise, only those tables which actually require a reorganization (for example, due to a cluster enlargement or a high percentage of rows marked as deleted) are reorganized.

  • Virtual tables cannot be reorganized.

This statement should be executed immediately after a cluster enlargement to prevent a negative impact on system performance.

The execution of this statement can take a long time as each COMMIT writes a lot of data to disk.

Running queries that involve large tables during the execution of this statement is not recommended, as several index creations in parallel may interfere with each other. This restriction does not apply to DML statements as they normally do not create indexes.

Example

REORGANIZE DATABASE;