Compression and Database In-Memory

54.1 Compression Types

Basic table compression (COMPRESS BASIC) Direct-path loads, read-only

Advanced Row Compression (ROW STORE COMPRESS ADVANCED) Advanced Compression option

Hybrid Columnar Compression (HCC) (COLUMN STORE COMPRESS FOR QUERY/ARCHIVE LOW/HIGH) Warehouses, archives Exadata, ZFS, ADB Index compression (prefix, advanced) Prefix in EE, Advanced in the option SecureFiles LOB compression and dedup

RMAN / Data Pump / Data Guard redo compression Backups, network

Advanced Compression (except BASIC RMAN) Heat Map + Automatic Data Optimization (ADO)

ILM policies (compress or move cold data automatically)

ALTER TABLE orders MOVE ROW STORE COMPRESS ADVANCED ONLINE;
EXEC DBMS_COMPRESSION.GET_COMPRESSION_RATIO(...);   -- Compression Advisor
ALTER SYSTEM SET heat_map = ON;
ALTER TABLE sales ILM ADD POLICY ROW STORE COMPRESS ADVANCED SEGMENT AFTER 30 DAYS OF NO MODIFICATION;

54.2 Database In-Memory (12.1.0.2+)

A dual-format architecture: row format in the buffer cache plus columnar format in the In-Memory Column Store (IM column store), kept transactionally consistent.

SIMD vector processing, In-Memory storage indexes, In-Memory join groups, In-Memory aggregation (vector transformation), In-Memory expressions.

ALTER SYSTEM SET inmemory_size = 16G SCOPE = SPFILE;   -- restart (can be increased dynamically later)
ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH;
ALTER TABLE sales INMEMORY NO INMEMORY (notes_col);
ALTER TABLE sales NO INMEMORY;
SELECT segment_name, populate_status, bytes_not_populated, inmemory_size FROM v$im_segments;
-- AUTOMATIC In-Memory (18c+): INMEMORY_AUTOMATIC_LEVEL
-- Base Level (19.8+): up to 16 GB free without the license