Rebuilding tables

You can reclaim disk space on a large scale by rebuilding tables, as follows:.

You can reclaim disk space on a large scale by rebuilding tables, as follows:

  1. Create a table with the same (or similar) definition as the table to rebuild.

  2. Create projections for the new table.

  3. Copy data from the target table into the new one with INSERT...SELECT.

  4. Drop the old table and its projections.

  5. Rename the new table with ALTER TABLE...RENAME, using the name of the old table.

Projection considerations

  • You must have enough disk space to contain the old and new projections at the same time. If necessary, you can drop some of the old projections before loading the new table. You must, however, retain at least one superprojection of the old table (or two buddy superprojections to maintain K-safety) until the new table is loaded. See Prepare disk storage locations for disk space requirements.

  • You can specify different names for the new projections or use ALTER TABLE...RENAME to change the names of the old projections.

  • The relationship between tables and projections does not depend on object names. Instead, it depends on object identifiers that are not affected by rename operations. Thus, if you rename a table, its projections continue to work normally.