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).