Compression Recommendations
It is highly recommended to use the following guidelines with regard to compression.
1. For all transactional data tables including ILM enabled tables (except D1_MSRMT* tables):
a. For easier operational manageability, it is recommended to enable the compression at tablespace level while creating separate tablespaces for each logical unit of archival (like a parent table partition and the corresponding referenced child table partitions).
b. Use securefile medium compression for LOBs.
c. On Oracle database 19c:
• Use advanced compression for table data compression.
• Compress indexes using advanced low compression (using ‘compress advanced low’ clause).
2. For D1_MSRMT* tables:
a. Keep current table partitions uncompressed for D1_MSRMT. Other D1_MSRMT* tables should use compressed tablespaces for all partitions.
b. For the D1_MSRMT table- Periodically (recommended monthly), compress the data by reloading into a staging table followed by partition exchange. It is highly recommended to use bulk load CTAS operation with parallel clause during the reload.
• Use ‘QUERY HIGH’ compression for Exadata implementations.
• For non-Exadata implementations, on 19c use ‘row store compress advanced’.
c. For indexes:
• On Oracle database 19c, compress indexes using advanced low compression (using ‘compress advanced low’ clause).