Standard advice is to rebuild the index on a regular basis.
Below is a summary of index compression on an Oracle database:
- If Index Compression is an internal database optimization that does not alter the table structure, it is okay to use it.
- If it does alter the table structure and does affect the results of SQL queries like retrieving back values from AC, it is NOT okay to use it. But to be sure, we really need to create a Test DB, log values, apply index compression, and then check if we can retrieve the values properly from AC for various charts.
- Gateway 2 specifically does not do anything to support/unsupport Index Compression.
- Node_ref_table and var_ref_table should not be altered.
For more information, refer to the following link: Oracle table reorganization: Online or Offline.