Oracle Table Compression: Row Store, Hybrid Columnar, and the Workload That Decides

An AWR report shows a 380 GB fact table accounting for the majority of physical reads. The application team requests a larger buffer cache. The infrastructure team declines — no budget. The DBA proposes compressing the table. The response is: “Compression is for storage savings. We have an I/O problem.”

That is the misconception the rest of this article addresses.

Oracle reads data in database blocks. If compression allows more rows to occupy each block, Oracle may need to read fewer blocks to satisfy the same query. The buffer cache then holds more useful rows per block it caches. Compression affects I/O and buffer-cache efficiency — not only disk capacity.

That said, compression consumes CPU. Whether the trade-off is favourable depends on the workload, the data, the access pattern, and the compression method chosen.

The answer

Oracle table compression reduces the number of blocks required to store a given dataset. Fewer blocks can mean fewer physical reads, reduced I/O bandwidth consumption, a lower buffer-cache footprint per row, and smaller backups. The compression method that achieves this varies significantly by workload:

Actively updated OLTP data
        → ROW STORE COMPRESS ADVANCED

Bulk-loaded, mostly-read data
        → ROW STORE COMPRESS

Query-intensive warehouse data  (on supported storage)
        → COLUMN STORE COMPRESS FOR QUERY LOW / HIGH

Cold or archival data  (on supported storage)
        → COLUMN STORE COMPRESS FOR ARCHIVE LOW / HIGH

Each option has different DML compatibility, CPU cost, compression ratio, licensing requirement, and — for Hybrid Columnar Compression — platform and storage dependencies. No single method is appropriate for every table.

What you will learn

• Why compression can reduce I/O and improve buffer-cache efficiency, not only save storage
• The taxonomy of Oracle compression methods and the SQL syntax that activates each
• How Basic and Advanced Row Compression differ in DML compatibility
• What Hybrid Columnar Compression is, how it differs from row-store compression, and why it requires specific storage
• How to choose compression based on workload characteristics
• How to inspect and apply compression to existing tables and partitions
• Licensing and platform dependencies that affect which methods are available
• How to choose compression based on workload type, DML frequency, and platform constraints

Scope: Oracle Database 12c through 23ai. Applies to on-premises, OCI Exadata-based services, and Exadata deployments. Hybrid Columnar Compression requires supported Oracle engineered storage — it is not available on standard OCI block volumes or commodity storage; this article identifies the constraint but defers the definitive platform list to current Oracle documentation. Licensing requirements for Advanced Row Compression and HCC apply to Enterprise Edition; verify against the current Oracle Database Licensing Information User Manual for the installed release. All compression ratios in this article are illustrative — actual ratios depend on data characteristics.

Why compression reduces I/O

Oracle reads and writes data in database blocks. A block that holds more rows requires fewer block reads to process the same number of rows.

Uncompressed table:
  Block 1: 10 rows
  Block 2: 10 rows
  Block 3: 10 rows
  Block 4: 10 rows
  → 4 blocks to read 40 rows

Compressed table (2× density):
  Block 1: 20 rows
  Block 2: 20 rows
  → 2 blocks to read 40 rows

The same 40 rows require half as many block reads. For a full table scan this translates directly into fewer physical I/Os and lower I/O bandwidth consumption. For the buffer cache, a fixed pool size now holds more useful row data.

10 GB buffer cache

Uncompressed: N rows represented across M blocks
Compressed:   potentially significantly more rows in the same M blocks

Compression does not enlarge the buffer cache. It increases the amount of useful table data representable within a fixed block count — which is a different and equally practical benefit.

The actual improvement depends on:

• Compression ratio achieved on this specific data
• Access path (full scan benefits more than single-row index lookup)
• Selectivity and index usage
• Cache warm state
• DML patterns introducing uncompressed rows
• CPU availability for decompression overhead

Do not apply a compression ratio to storage and conclude that I/O will improve by the same factor. The relationship is real but not linear across all access patterns.

Compression families

Oracle provides two structural families of table compression. The SQL syntax names reflect the storage model:

Row Store Compression
├── ROW STORE COMPRESS
│   (Basic Table Compression)
│
└── ROW STORE COMPRESS ADVANCED
    (Advanced Row Compression — historically called OLTP Compression)

Column Store Compression  (Hybrid Columnar Compression — HCC)
├── COLUMN STORE COMPRESS FOR QUERY LOW
├── COLUMN STORE COMPRESS FOR QUERY HIGH
├── COLUMN STORE COMPRESS FOR ARCHIVE LOW
└── COLUMN STORE COMPRESS FOR ARCHIVE HIGH

Oracle documentation may use either the SQL keyword form or the descriptive name. Both refer to the same features. COMPRESS without qualifiers in older syntax maps to basic row compression; current best practice uses the explicit ROW STORE COMPRESS syntax.

ROW STORE COMPRESS

Basic Table Compression stores duplicate values within a block more efficiently by eliminating repetition at the block level. A block containing many rows with the same STATUS, REGION, or PRODUCT_CODE value stores the repeated value once and references it from each row.

CREATE TABLE sales_archive
(
    sale_id     NUMBER,
    customer_id NUMBER,
    sale_date   DATE,
    amount      NUMBER
)
ROW STORE COMPRESS;

Apply to an existing table:

ALTER TABLE sales_archive ROW STORE COMPRESS;

DML behaviour. This is the critical operational constraint. Regular DML — single-row INSERT, UPDATE, DELETE — writes rows in uncompressed format. Compression occurs during direct-path operations: INSERT /*+ APPEND */, CREATE TABLE AS SELECT, INSERT ... SELECT with direct path, SQL*Loader direct path, and online segment rebuilds. Blocks populated through conventional DML may remain uncompressed until a bulk operation or table move triggers reorganisation.

Appropriate workload. Bulk-loaded tables with limited post-load DML. Staging tables. Historical partitions that have been frozen after a batch load. Data warehouse dimension tables loaded by ETL.

Inappropriate workload. OLTP tables with frequent single-row inserts, updates, or deletes — those rows remain uncompressed, and the benefit is inconsistent.

Setting the attribute does not recompress existing blocks. An ALTER TABLE ... ROW STORE COMPRESS statement updates the table’s metadata. Blocks already on disk remain in their current format. To recompress existing data, the segment must be moved or rebuilt — see the section on applying compression to existing data.

ROW STORE COMPRESS ADVANCED

Advanced Row Compression is designed for tables under active DML. Oracle applies compression logic transparently during all DML operations — conventional inserts, updates, and deletes — not only during direct-path bulk loads.

CREATE TABLE orders
(
    order_id    NUMBER,
    customer_id NUMBER,
    order_date  DATE,
    status      VARCHAR2(20)
)
ROW STORE COMPRESS ADVANCED;

The mechanism works at the block level: Oracle identifies duplicate column values within a block and stores them in a symbol table within the block header, replacing repeated occurrences with references. This process operates on blocks as they are written, without requiring a bulk-load trigger.

Why this can reduce I/O on an OLTP system:

More rows per block
        ↓
Fewer blocks to store the same dataset
        ↓
Fewer block reads for range scans and lookups
        ↓
Lower physical I/O
        ↓
Better buffer-cache utilisation per byte of cache

CPU trade-off. Every write operation involves compression work. On CPU-bound systems this overhead is measurable. On I/O-bound systems the net effect is often positive — fewer I/Os offset the compression CPU cost. Test under representative workload before drawing conclusions.

Licensing. Advanced Row Compression is part of the Oracle Advanced Compression option. Verify the current Oracle Database Licensing Information User Manual for the installed release and edition.

On an I/O-bound OLTP table where SQL cannot be changed and the buffer cache cannot be increased, Advanced Row Compression addresses the block-count problem without requiring any application changes. Basic row compression is not the right choice in this scenario — it does not maintain compression under regular DML and would leave most OLTP-written blocks uncompressed.

Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a fundamentally different architecture from row-store compression. It is available only on Oracle-qualified storage and platforms.

Within HCC, data is organised into compression units (CUs). A compression unit spans multiple Oracle blocks and contains column values from many rows grouped together. Because values from the same column tend to have similar characteristics — repeated status codes, bounded numeric ranges, repeated date patterns — column-oriented grouping within the CU achieves much higher compression ratios than block-level row deduplication.

Row Store (block-level):
  Block contains row1(col1, col2, col3), row2(col1, col2, col3), ...
  Deduplicate within block

HCC (compression unit):
  CU contains col1 values for rows 1–10000, col2 values for rows 1–10000, ...
  Column-oriented compression within CU

This is not a standalone columnar database architecture. The table remains an Oracle heap-organized table visible through standard SQL. The internal storage organization within each compression unit is what differs.

DML implication. When a row in an HCC-compressed segment is modified by regular DML, Oracle migrates that row out of the compression unit into standard row format within the block. The compression unit itself is not rewritten. Over time, active DML progressively decompresses HCC blocks at the row level. HCC is therefore not appropriate for tables subject to frequent update or delete activity. It is appropriate for write-once-or-rarely data.

Platform and storage requirement. HCC requires specific Oracle engineered storage infrastructure. Supported platforms as documented by Oracle include:

• Oracle Exadata Database Machine (including ExaCS and ExaCC on OCI)
• Oracle ZFS Storage Appliance (via Direct NFS or ASM)
• Oracle FS1 Flash Storage System
• Oracle SPARC SuperCluster

Attempting to use HCC syntax on unsupported storage raises:

ORA-64307: Exadata Hybrid Columnar Compression is not supported
           for tablespaces on this storage type

Standard OCI block volumes, Oracle Database Appliance (ODA), and commodity SAN or NAS storage do not support HCC. The Oracle Exadata Cloud Service (ExaCS) and ExaCC on OCI do support HCC — but standard OCI Compute instances with block storage do not, even when running Oracle Enterprise Edition. Verify the current supported-platforms list in Oracle documentation before planning any HCC deployment.

COLUMN STORE COMPRESS FOR QUERY LOW

Optimised for query performance. Provides high compression with moderate compression overhead. Lower CPU cost per read operation than QUERY HIGH.

CREATE TABLE sales_history
(
    sale_id     NUMBER,
    customer_id NUMBER,
    sale_date   DATE,
    region      VARCHAR2(30),
    amount      NUMBER
)
COLUMN STORE COMPRESS FOR QUERY LOW;

Appropriate for: large data warehouse fact tables queried frequently, where query response time and compression are both important and the data is not subject to regular DML after loading.

COLUMN STORE COMPRESS FOR QUERY HIGH

Higher compression ratio than QUERY LOW, at greater compression and decompression CPU cost. Appropriate when stronger compression is required and CPU headroom exists.

Use when storage reduction is more important than minimising per-query CPU overhead, but the data still needs to be accessible for regular queries.

COLUMN STORE COMPRESS FOR ARCHIVE LOW

Very high compression for data that is accessed rarely. The decompression cost per access is significant; this is acceptable when access frequency is low enough that CPU overhead is not a practical concern.

Appropriate for: historical partitions that have passed out of the active query window and are retained for regulatory or audit purposes.

COLUMN STORE COMPRESS FOR ARCHIVE HIGH

Maximum-oriented compression. Highest CPU cost for both write and read. Intended for cold data where storage reduction is the primary objective and access is exceptional rather than routine.

HCC option trade-off:

QUERY LOW        ←— lower CPU cost, lower compression ratio
QUERY HIGH
ARCHIVE LOW
ARCHIVE HIGH     ←— higher CPU cost, higher compression ratio

Do not assume ARCHIVE HIGH is universally the best choice because it produces the smallest segments. If the data is queried regularly, the decompression cost at ARCHIVE HIGH will be measurable.

Comparison

HCC methods require Oracle Advanced Compression option and supported Oracle storage or platform.

Examining existing compression

-- Table-level compression
SELECT table_name,
       compression,
       compress_for
FROM   dba_tables
WHERE  owner = 'SALES_OWNER'
ORDER BY table_name;

-- Partition-level compression
SELECT partition_name,
       compression,
       compress_for
FROM   dba_tab_partitions
WHERE  table_owner = 'SALES_OWNER'
AND    table_name  = 'SALES'
ORDER BY partition_position;

-- Subpartition-level compression
SELECT subpartition_name,
       compression,
       compress_for
FROM   dba_tab_subpartitions
WHERE  table_owner = 'SALES_OWNER'
AND    table_name  = 'SALES'
ORDER BY subpartition_position;

The COMPRESS_FOR column returns the compression method name as stored by Oracle: BASIC, ADVANCED, QUERY LOW, QUERY HIGH, ARCHIVE LOW, ARCHIVE HIGH, or NULL for uncompressed. Note the values use spaces, not underscores (QUERY LOW not QUERY_LOW), and Advanced Row Compression is stored as ADVANCED — not FOR OLTP or OLTP, even though that older DDL alias is still accepted.

Estimating compression before implementing. DBMS_COMPRESSION.GET_COMPRESSION_RATIO analyses a sample of table data and returns estimated compression ratios for each compression type. Run this against a representative data sample before committing to a compression strategy — actual ratios vary significantly by data content, column types, cardinality, and repetition patterns.

DECLARE
    blkcnt_cmp   PLS_INTEGER;
    blkcnt_uncmp PLS_INTEGER;
    row_cmp      PLS_INTEGER;
    row_uncmp    PLS_INTEGER;
    cmp_ratio    NUMBER;
    comptype_str VARCHAR2(200);
BEGIN
    DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
        scratchtbsname => 'USERS',
        ownname        => 'SALES_OWNER',
        objname        => 'SALES',
        subobjname     => NULL,
        comptype       => DBMS_COMPRESSION.COMP_ADVANCED,
        blkcnt_cmp     => blkcnt_cmp,
        blkcnt_uncmp   => blkcnt_uncmp,
        row_cmp        => row_cmp,
        row_uncmp      => row_uncmp,
        cmp_ratio      => cmp_ratio,
        comptype_str   => comptype_str
    );
    DBMS_OUTPUT.PUT_LINE('Compression ratio: ' || cmp_ratio);
    DBMS_OUTPUT.PUT_LINE('Method: ' || comptype_str);
END;
/

Key comptype constants (Oracle 19c): DBMS_COMPRESSION.COMP_ADVANCED (2), DBMS_COMPRESSION.COMP_BASIC (4096), DBMS_COMPRESSION.COMP_QUERY_LOW (8), DBMS_COMPRESSION.COMP_QUERY_HIGH (4), DBMS_COMPRESSION.COMP_ARCHIVE_LOW (32), DBMS_COMPRESSION.COMP_ARCHIVE_HIGH (16).

DBMS_COMPRESSION requires a scratch tablespace with sufficient space for the analysis sample. The result is an estimate; validate it against a full-scale test before production deployment.

Applying compression to existing data

Setting a compression attribute on an existing table does not rewrite existing blocks. The attribute controls how future blocks are written.

To compress data already stored in the segment, the segment must be reorganised. The primary method is a table move:

-- Offline move (table is locked during the operation)
ALTER TABLE sales ROW STORE COMPRESS ADVANCED;
ALTER TABLE sales MOVE;

Or set compression and move in a single statement:

ALTER TABLE sales MOVE ROW STORE COMPRESS ADVANCED;

For large tables, use the online variant to reduce application impact:

ALTER TABLE sales MOVE ONLINE ROW STORE COMPRESS ADVANCED;

Online move is available from Oracle 12.2 and later. With the ONLINE keyword, Oracle automatically maintains all indexes as usable throughout the operation — no post-move index rebuild is required. Without ONLINE, an offline move marks all indexes UNUSABLE and they must be rebuilt explicitly.

Confirm index state after any offline move:

SELECT index_name, status
FROM   dba_indexes
WHERE  table_owner = 'SALES_OWNER'
AND    table_name  = 'SALES';

For partitioned tables, move individual partitions:

ALTER TABLE sales MOVE PARTITION p_archive
    COLUMN STORE COMPRESS FOR ARCHIVE HIGH;

After an offline partition move, local indexes on that partition become UNUSABLE. Rebuild them explicitly or include UPDATE INDEXES or UPDATE GLOBAL INDEXES in the move statement where supported.

Online move limitations. ALTER TABLE ... MOVE ONLINE cannot be used on Index-Organized Tables with certain LOB or object-type columns, or when a domain index exists on the table. Parallel DML and direct-path inserts are not permitted during an ongoing online move. Test in a non-production environment before applying to production.

Partition-level compression

Different partitions on the same table can use different compression methods. This is the foundation of Information Lifecycle Management (ILM) with Oracle partitioning.

CREATE TABLE sales
(
    sale_id     NUMBER        NOT NULL,
    customer_id NUMBER        NOT NULL,
    sale_date   DATE          NOT NULL,
    region      VARCHAR2(30),
    amount      NUMBER(12,2)
)
PARTITION BY RANGE (sale_date)
(
    PARTITION p_archive    VALUES LESS THAN (DATE '2023-01-01')
        COLUMN STORE COMPRESS FOR ARCHIVE HIGH,

    PARTITION p_historical VALUES LESS THAN (DATE '2025-01-01')
        COLUMN STORE COMPRESS FOR QUERY LOW,

    PARTITION p_current    VALUES LESS THAN (MAXVALUE)
        ROW STORE COMPRESS ADVANCED
);

Data lifecycle mapped to compression method:

Current partition (active DML)
    → ROW STORE COMPRESS ADVANCED
          ↓ partition switch / interval partition aging
Historical partition (queries, minimal DML)
    → COLUMN STORE COMPRESS FOR QUERY LOW
          ↓ partition aging
Archive partition (cold, regulatory retention)
    → COLUMN STORE COMPRESS FOR ARCHIVE HIGH

A partition’s compression method can be changed independently without affecting other partitions. Moving an aging partition to a higher compression tier is a standard ILM operation.

HCC partitions inherit the storage and platform requirement. A partitioned table mixing ROW STORE COMPRESS ADVANCED (available on standard storage) and COLUMN STORE COMPRESS FOR QUERY LOW (requires supported storage/platform) cannot be created on infrastructure that does not support HCC — even if only some partitions use HCC.

Compression and indexes

Table compression and index compression are separate features.

Compressing a table does not compress its associated indexes. Index blocks are not subject to the same row-store or column-store compression that applies to table blocks. Oracle provides separate index compression features — including COMPRESS on B-tree indexes and Advanced Index Compression — which operate independently of table compression.

When evaluating whether compression reduces the storage footprint or I/O requirements for a workload, account for both table segments and index segments. On an index-heavy schema, index segment size can equal or exceed table segment size.

When evaluating whether compression reduces the overall storage footprint, account for index segments separately — table compression does not extend to them.

Licensing and platform requirements

Oracle compression features have licensing implications that vary by edition, version, and feature. The following is a structural overview — always verify against the current Oracle Database Licensing Information User Manual for the installed release before deployment.

These are not the same three things:

• Feature availability — whether the Oracle software supports the syntax
• Licensing requirement — whether the deployment is licensed to use the feature
• Platform/storage requirement — whether the storage infrastructure supports the feature (HCC)

A feature can be syntactically available in the Oracle binary, unlicensed for the current deployment, and unsupported on the current storage — simultaneously. Deploying Advanced Row Compression or HCC without the appropriate Oracle Advanced Compression license is a compliance issue regardless of whether the feature functions.

For Standard Edition 2, compression option availability is substantially more limited than Enterprise Edition. Consult Oracle’s licensing documentation for the specific SE2 restrictions on the installed version.

Choosing the right method

A workload-based decision path:

Is the table subject to frequent DML (INSERT/UPDATE/DELETE)?
              |
         +----+----+
         |         |
        Yes        No
         |          |
Advanced Row    What is the primary access pattern?
Compression         |
ROW STORE      +----+----+
COMPRESS       |         |
ADVANCED     Queries   Archival/
               |       cold data
           Does supported   |
           Oracle storage  ARCHIVE compression
           exist?          (LOW or HIGH based
               |           on CPU tolerance)
          +----+----+
          |         |
         Yes        No
          |          |
     QUERY LOW/  ROW STORE
     QUERY HIGH  COMPRESS
     (based on   (if bulk-
      CPU        loaded) or
      tolerance) COMPRESS
                 ADVANCED

OLTP table under I/O pressure

An active orders table is causing I/O pressure during peak hours. DML frequency is high. SQL cannot be changed; the buffer cache cannot be increased.

Active DML rules out HCC. Basic row compression does not maintain compression under conventional DML. ROW STORE COMPRESS ADVANCED is the correct choice — it compresses blocks during all DML operations and reduces block count without requiring application changes. Confirm the Advanced Compression license is in place.

Data warehouse fact table on Exadata

A 2 TB fact table is loaded nightly by ETL. Queries run throughout the day. No updates after load. The environment runs on Exadata.

No post-load DML means HCC is viable. Query performance matters, so the ARCHIVE family is not appropriate. Start with COLUMN STORE COMPRESS FOR QUERY LOW — it delivers high compression with moderate CPU cost. Move to QUERY HIGH only if greater compression is needed and CPU headroom has been confirmed under the production query workload.

Historical regulatory data, rarely queried

A compliance partition holds seven years of transaction history queried for audits once or twice per year. Storage cost is the primary concern.

Rare access means decompression CPU cost is acceptable. COLUMN STORE COMPRESS FOR ARCHIVE HIGH delivers maximum-oriented compression for cold data. Confirm the platform supports HCC before implementation.

Bulk-loaded staging table, standard storage, no Advanced Compression license

A staging table is populated nightly by direct-path SQL*Loader and read by downstream ETL. No post-load DML. Standard NFS storage. Advanced Compression is not licensed.

No Advanced Compression license rules out Advanced Row Compression and HCC. Standard storage rules out HCC regardless. Direct-path loading means Basic compression applies during the load. ROW STORE COMPRESS is appropriate.

Reducing effective buffer-cache consumption without adding memory

A table is causing repeated physical reads despite fitting comfortably within the total buffer cache budget on paper.

Compression increases the number of rows per block, not the number of blocks the cache holds. The same cache now represents more useful row data. Apply the compression method appropriate to the table’s DML pattern — compression does not enlarge the cache, it increases the data density within it.

Do not do this

Do not treat compression as a buffer-cache substitute. Compression increases data density within cached blocks — it does not enlarge the cache. These are different mechanisms with different costs and benefits.

• Do not assume compression eliminates I/O. It reduces block count for suitable access patterns — a full table scan benefits far more than a single-row index lookup.
• Do not use ROW STORE COMPRESS on an actively updated OLTP table. Conventional DML writes uncompressed rows; the benefit will be inconsistent and may be negligible.
• Do not choose QUERY HIGH over QUERY LOW without testing. Higher compression ratio means higher CPU cost per read — on heavily queried data this can reduce overall throughput.
• Do not apply ARCHIVE HIGH to data that is queried regularly. Archive-level decompression cost is significant; it is appropriate only for cold data where reads are exceptional.
• Do not attempt HCC on standard OCI block volumes, ODA, or commodity SAN/NAS storage. Oracle raises ORA-64307. HCC requires Oracle engineered storage — Exadata, ZFS Storage Appliance, FS1, SPARC SuperCluster, or OCI ExaCS/ExaCC.
• Do not query DBA_TABLES.COMPRESS_FOR for the value FOR OLTP. The COMPRESS FOR OLTP DDL alias is accepted but stored as ADVANCED. Scripts must use 'ADVANCED'.
• Do not assume that setting a compression attribute recompresses existing data. Blocks already written remain in their current format until the segment is moved or rebuilt.
• Do not assume table compression extends to indexes. Index segments require separate compression configuration.
• Do not enable Advanced Row Compression or HCC without confirming the Oracle Advanced Compression license is in place. Oracle may not error at DDL time; the absence of an error is not a licensing confirmation.

Official references

• DBMS_COMPRESSION — Oracle Database PL/SQL Packages and Types Reference 19c
• ALL_TABLES / DBA_TABLES — Oracle Database Reference 19c
• DBA_TAB_PARTITIONS — Oracle Database Reference 19c
• Managing Tables — Oracle Database Administrator’s Guide 19c
• Tables and Table Clusters — Oracle Database Concepts 19c
• VLDB and Partitioning Guide — Oracle Database 19c
• Hybrid Columnar Compression — Oracle Exadata Documentation
• Oracle Database Licensing Information User Manual 19c
• Oracle Advanced Compression

Continue reading

• Oracle STATSPACK: Installation, Snapshot Management, and Report Interpretation
• Oracle Hard Parses: How to Find and Fix SQL That Refuses to Share Cursors

Marios Pavlidis Principal Database Administrator