Table/Index Compression Concepts, Heat Map/ILM Awareness, and Multi-Terabyte Design

Learning outcomes

ServiceHub retains several years of event payloads. Storage growth is expensive, but most old rows are rarely updated. Compression can reduce physical blocks and I/O, yet aggressive methods can increase CPU, complicate DML and require paid options or specific storage. A VLDB therefore needs a tiered lifecycle, not one “compress everything” setting.

1. Basic compression favors bulk/static data; advanced row compression supports conventional DML

2. Free compression lab: same data, three storage policies

BEGIN  FOR t IN (    SELECT table_name    FROM user_tables    WHERE table_name IN (      'SH19_COMP_NONE','SH19_COMP_BASIC','SH19_COMP_ADV'    )  ) LOOP    EXECUTE IMMEDIATE 'DROP TABLE '||t.table_name||' PURGE';  END LOOP;END;/CREATE TABLE sh19_comp_noneNOCOMPRESSASSELECT  LEVEL AS event_id,  1+MOD(LEVEL,12) AS region_id,  CASE MOD(LEVEL,4)    WHEN 0 THEN 'OPEN'    WHEN 1 THEN 'CLOSED'    WHEN 2 THEN 'HOLD'    ELSE 'ASSIGNED'  END AS status_code,  RPAD('servicehub-repeated-payload-',180,'x') AS payloadFROM dualCONNECT BY LEVEL <= 50000;CREATE TABLE sh19_comp_basicROW STORE COMPRESS BASICAS SELECT * FROM sh19_comp_none;CREATE TABLE sh19_comp_advROW STORE COMPRESS ADVANCEDAS SELECT * FROM sh19_comp_none;

CTAS/direct-path style population gives Basic compression a fair load path. This is a small functional demonstration, not a production compression-ratio benchmark.

3. Observe metadata and allocated segment bytes

SELECT  t.table_name,  t.compression,  t.compress_for,  s.bytes,  s.blocksFROM user_tables tJOIN user_segments s  ON s.segment_name=t.table_name AND s.segment_type='TABLE'WHERE t.table_name IN (  'SH19_COMP_NONE','SH19_COMP_BASIC','SH19_COMP_ADV')ORDER BY t.table_name;

Segment bytes are extent allocation, so small tables can round to identical extents even when block occupancy differs. Do not publish a universal “X:1 compression ratio” from this toy dataset. Production tests should use representative column cardinality, row width, DML, indexes, LOBs and backup behavior.

4. Advanced row compression changes ongoing DML behavior

INSERT INTO sh19_comp_advVALUES(60001,3,'OPEN',RPAD('new-payload-',180,'n'));UPDATE sh19_comp_advSET status_code='CLOSED'WHERE event_id BETWEEN 100 AND 200;COMMIT;SELECT COUNT(*) FROM sh19_comp_adv;

Compression is transparent to SQL semantics but not free CPU. Whether it improves response time depends on the trade between fewer I/O/cache blocks and compression/decompression/DML work.

5. Index compression is a separate physical decision

CREATE INDEX sh19_comp_ixON sh19_comp_adv(region_id,status_code,event_id)COMPRESS 2;SELECT index_name,compression,prefix_lengthFROM user_indexesWHERE index_name='SH19_COMP_IX';

Prefix compression is useful only when repeated leading key values make it worthwhile. Advanced Index Compression has different algorithms/licensing; test index size, leaf splits, query CPU and DML before adopting it.

6. HCC is not a generic Free filesystem feature

Hybrid Columnar Compression stores rows in compression units using a columnar-within-unit representation. COLUMN STORE COMPRESS FOR QUERY targets warehouse scan efficiency; FOR ARCHIVE targets colder storage. The current licensing matrix marks HCC unavailable in Free. For on-prem EE it requires supported Oracle storage such as ZFS/Axiom/FS1 according to the current matrix; Exadata/cloud-engineered offerings have their own entitlements.

CREATE TABLE servicehub_cold_history (  event_id NUMBER,  event_ts DATE,  payload VARCHAR2(200))COLUMN STORE COMPRESS FOR ARCHIVE HIGH;

HCC DML behavior and row-level locking support depend on platform/options; cold/append-mostly partitions are the usual architectural fit.

7. Heat Map records access/modification temperature

Oracle Heat Map tracks segment/block access and modification timestamps for Information Lifecycle Management (ILM) decisions. It requires HEAT_MAP=ON and COMPATIBLE >= 12.0.0. Current licensing marks Heat Map unavailable in Free.

ALTER SYSTEM SET HEAT_MAP=ON SCOPE=BOTH;SELECT name,value,ispdb_modifiableFROM v$parameterWHERE name='heat_map';

Turning Heat Map on is a system policy/overhead decision, not a prerequisite for manual partition tiering.

8. Automatic Data Optimization converts history into policy actions

Automatic Data Optimization (ADO) evaluates ILM policies such as “after N days with no modification, compress this segment” and can tier/compress eligible data. Current 26ai licensing marks ADO unavailable in Free; on EE/EE-ES it requires Advanced Compression or Database In-Memory.

ALTER TABLE servicehub_historyILM ADD POLICY  ROW STORE COMPRESS ADVANCED  SEGMENT  AFTER 30 DAYS OF NO MODIFICATION;

ADO automates a policy—it does not decide whether 30 days is correct. Retention/compression tiers must come from business access, backup/recovery, legal hold and cost evidence.

9. Deliberately wrong: compress every hot OLTP partition with the strongest method

Maximum compression can increase CPU, amplify update work or require a platform that is inappropriate for the hot tier. A frequently updated current-month partition may benefit from NOCOMPRESS/Advanced Row Compression, while six-month-old append-only partitions can use stronger compression. The right answer can differ by partition.

10. Multi-terabyte lifecycle is a system, not just table DDL

• Partition size: aligned to ingest/retention and manageable backup/restore/maintenance units.
• Indexes: local where lifecycle independence matters; global only where cross-partition access justifies maintenance cost.
• Statistics: partition/global stats strategy tested after load/exchange.
• Backup: RMAN retention/archive-log design and restore drills account for data growth/compression.
• Storage: tablespace/ASM/cloud tier placement and headroom measured rather than inferred from logical table size.
• Recovery: partition/tablespace/PDB failure scope mapped to actual RPO/RTO.
• Security: encrypted backups/TDE and offsite copies retained with keys.

11. Cleanup

DROP INDEX sh19_comp_ix;DROP TABLE sh19_comp_none PURGE;DROP TABLE sh19_comp_basic PURGE;DROP TABLE sh19_comp_adv PURGE;

12. Production judgment and chapter close

Compression should reduce total system cost under the real workload—not merely table bytes. Benchmark representative reads, updates, loads, backups and restore time. Use stronger/costlier methods on colder partitions when platform/licensing support them. Heat Map/ADO can automate tiering on entitled systems but should encode an already-approved lifecycle policy.

Current 26ai: Basic Table Compression is available in Free; Advanced Compression is included in Free but extra-cost on EE/EE-ES; Heat Map and ADO are not available in Free; HCC is not available in Free and remains storage/offering constrained. No restart or COMPATIBLE increase is required for the Free compression lab. This closes Chapter 19 by connecting partition lifecycle, throughput and storage economics into one VLDB design.

Authoritative references

• Managing Tables — Compression — basic/advanced/HCC and ILM concepts
• Information Lifecycle Management — Heat Map and ADO lifecycle
• CREATE TABLE — row/HCC compression clauses
• Indexes — index compression concepts
• Licensing Information — compression/Heat Map/ADO/HCC feature matrix