RECOMPRESS

Purpose

Use this statement to improve the compression of tables.

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

recompress::=

Recompress Statement

Usage Notes

This command implies a COMMIT before and after recompressing any of the specified tables, except if you use the single table alternative RECOMPRESS TABLE.

  • The execution time of this command can take some time. A smart logic tries to only recompress those columns where a significant improvement can be achieved, however, you can enforce the recompression using the ENFORCE option.
  • A recompression of a table makes sense after inserting a big amount of new data. After that, the compression ratios do not need to be significantly better than before.
  • You can also specify certain columns to partially compress a table.
  • The compressed and raw data size of a table is available in the system table EXA_DBA_OBJECT_SIZES.

Examples

RECOMPRESS TABLE t1 (column_1);
RECOMPRESS TABLES t2,t3 ENFORCE;