Use this statement to improve the compression of tables.
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.
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.