Advanced Compression & In-memory segment size comparison (Oracle: 19.8)

 Table Records: 31M

SQL> select 'OSB_CPU_LOAD_OCT2020' as tname,  count(*) as rowcount from OSB_CPU_LOAD_OCT2020
  2  union
  3* select 'OSB_CPU_LOAD_OCT2020_NOCOMP' as tname,  count(*) as rowcount from OSB_CPU_LOAD_OCT2020_NOCOMP;

                         TNAME    ROWCOUNT
______________________________ ___________
OSB_CPU_LOAD_OCT2020            31,492,705
OSB_CPU_LOAD_OCT2020_NOCOMP     31,492,705

Enable In-memory and compression (in-memory compression is part of in-memory option, instead of advanced compression option.)

alter table donghua.OSB_CPU_LOAD_OCT2020 inmemory priority low memcompress for query low;
alter table donghua.OSB_CPU_LOAD_OCT2020_NOCOMP inmemory priority low memcompress for capacity high;

Check the table/in-memory compression setting

SQL> select table_name, compress_for,inmemory_compression from user_tables
  2  where table_name like 'OSB_CPU_LOAD_OCT2020%';


                    TABLE_NAME    COMPRESS_FOR    INMEMORY_COMPRESSION
______________________________ _______________ _______________________
OSB_CPU_LOAD_OCT2020           ADVANCED        FOR QUERY LOW
OSB_CPU_LOAD_OCT2020_NOCOMP                    FOR CAPACITY HIGH


Elapsed: 00:00:00.006

Verify the table on disk size: (compression is 43% smaller)

SQL> select segment_name, bytes from user_segments
  2* where segment_name like 'OSB_CPU_LOAD_OCT2020%';


                  SEGMENT_NAME         BYTES
______________________________ _____________
OSB_CPU_LOAD_OCT2020             620,756,992
OSB_CPU_LOAD_OCT2020_NOCOMP    1,073,741,824

Verify the table on in-memory size: (compression is 2x-6.5x smaller)

SQL> SELECT owner, segment_name, populate_status,
  2       inmemory_size, bytes_not_populated
  3*      FROM v$im_segments;


     OWNER                   SEGMENT_NAME    POPULATE_STATUS    INMEMORY_SIZE    BYTES_NOT_POPULATED
__________ ______________________________ __________________ ________________ ______________________
DONGHUA    OSB_CPU_LOAD_OCT2020_NOCOMP    COMPLETED               166,789,120                      0
DONGHUA    OSB_CPU_LOAD_OCT2020           COMPLETED               496,173,056                      0

Reference: https://blogs.oracle.com/in-memory/database-in-memory-compression