Columnstore tables and indexes in practice|V5.0.1|OceanBase Database|docs|OceanBase Documentations

Columnstore tables and indexes in practice

Last Updated：2026-09-04 11:28:24 Updated

This topic describes columnstore tables and indexes in OceanBase Database, as well as their combination with rowstore tables and indexes. It provides practical examples of table and index creation, along with query execution, for the payment ledger scenario, helping you apply the design choices to specific SQL statements.

Concepts

Columnstore table: The primary table is stored in columnstore format by default. You can create it using WITH COLUMN GROUP(each column) (pure columnstore) or WITH COLUMN GROUP(all columns, each column) (hybrid rowstore-columnstore). For more information, see Overview of columnstore storage — How to create columnstore and hybrid rowstore-columnstore tables.
Columnstore index: The index table itself is stored in columnstore format, not as a special index type on top of a columnstore table. When creating an index, specify WITH COLUMN GROUP(each column) to indicate it is a columnstore index. The index table can also have rowstore or columnstore column groups specified.
Rowstore index: The index table is organized in rowstore format (default or WITH COLUMN GROUP(all columns)), which is suitable for point queries and covering index backtracking paths.

The following examples all use the same business wide table payment_ledger (payment ledger): fields include order_id, pay_time, pay_channel, txn_status, amount, etc., facilitating comparison of read and write characteristics under different combinations.

Scenario combinations

Scenario	Applicable businesses	Policy	Advantages	Disadvantages
Row-based table + columnstore index	The business is mainly transactional, with some analytical query requirements, and the tables are large wide tables.	Create a columnstore index to improve the performance of analytical queries.	Only some columns are stored redundantly, and the storage format is columnar.	You need to identify appropriate columns to create a columnstore index based on the query statement. Moreover, index table data must be maintained during data writes, which results in relatively lower write performance.
Columnstore table + rowstore index	Primarily analytical workloads, but requiring support for efficient simple queries (such as point queries).	To avoid table access during query execution, you can create a covering index when creating an index.	Only some column data is stored redundantly. Currently, converting a columnar table to a row-based table or a hybrid row-column table online is not supported. You can add row-based indexes to optimize queries.	You need to identify appropriate fields based on the query statement to create a row-store index. Moreover, index table data must be maintained during data writes, which results in relatively lower write performance.
Hybrid row-column table	It needs to handle both transactional and analytical queries in a diverse manner.	You can directly redundantly store two copies of baseline data, allowing the system to generate appropriate execution plans based on query characteristics. By default, range scans use the columnar storage mode, while point queries fall back to the row-based storage mode.	Analysis queries do not require analysis of business query characteristics, as their results are strongly consistent.	This will consume more disk space and may lead to inaccurate execution plans.
Pure columnar storage table (without indexes)	AP scenarios	Use`WITH COLUMN GROUP(each column)`Create a columnar storage table to store data in columnar format.	High efficiency in bulk import and scanning of wide tables with few columns	Slow query by primary key/order number

Examples

Example 1: Rowstore table + Columnstore index

The core payment process involves INSERT operations and status checks by order_id. Operational reports summarize amount by channel and date using SUM/COUNT. The table has many columns, but analysis typically scans only a few.

Table and columnstore index creation:

CREATE TABLE payment_ledger (
    txn_id       BIGINT        NOT NULL,
    order_id     BIGINT        NOT NULL,
    user_id      BIGINT        NOT NULL,
    pay_time     DATETIME      NOT NULL,
    pay_channel  VARCHAR(32)   NOT NULL,
    txn_status   TINYINT       NOT NULL,
    amount       DECIMAL(18,2) NOT NULL,
    merchant_id  BIGINT        NOT NULL,
    remark       VARCHAR(256)  DEFAULT NULL,
    PRIMARY KEY (order_id, pay_time, txn_id)
)
PARTITION BY HASH(order_id) PARTITIONS 64
WITH COLUMN GROUP(all columns);

-- Columnstore index: Analyze frequently used columns for redundancy to avoid full-column scans in wide tables.
CREATE INDEX idx_pay_analytics
    ON payment_ledger (pay_time, pay_channel, txn_status, amount)
    WITH COLUMN GROUP(each column);

Typical TP query (uses the primary table's rowstore format + primary key):

SELECT txn_id, txn_status, amount, pay_channel
FROM payment_ledger
WHERE order_id = 10086001
ORDER BY pay_time DESC
LIMIT 1;

Typical analytical query (the optimizer may choose the columnstore index, scanning only the indexed columns):

SELECT pay_channel,
       SUM(amount) AS succ_amt,
       COUNT(*)    AS succ_cnt
FROM payment_ledger
WHERE pay_time >= '2025-01-01'
  AND pay_time <  '2025-02-01'
  AND txn_status = 1
GROUP BY pay_channel;

Keeping the primary table in rowstore format ensures high-concurrency writes and point queries are not affected. Analytical load is handled by the columnstore index, which is more stable for TP than converting the entire table to pure columnstore. Index columns should cover the fields needed for filtering and aggregation to avoid unnecessary wide-row scans when returning data to the primary table.

Example 2: Columnstore table + Rowstore index (mainly analytical, supplemented by point queries)

Transaction data is primarily imported in batches or reconciled. Daily SQL operations mostly involve full-partition scans and aggregations; only scenarios like customer service or risk control require single-transaction lookups by order_id.

Table creation and rowstore index creation:

CREATE TABLE payment_ledger_cs (
    txn_id       BIGINT        NOT NULL,
    order_id     BIGINT        NOT NULL,
    pay_time     DATETIME      NOT NULL,
    pay_channel  VARCHAR(32)   NOT NULL,
    txn_status   TINYINT       NOT NULL,
    amount       DECIMAL(18,2) NOT NULL,
    merchant_id  BIGINT        NOT NULL,
    PRIMARY KEY (txn_id, pay_time)
)
PARTITION BY RANGE COLUMNS(pay_time) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00')
)
WITH COLUMN GROUP(all columns, each column);

-- Rowstore index + STORING: Avoids wide-row access to the primary table during point queries
CREATE INDEX idx_order_lookup
    ON payment_ledger_cs (order_id)
    STORING (txn_status, amount, pay_time, pay_channel)
    WITH COLUMN GROUP(all columns);

Analytical query (primary table in columnstore format):

SELECT merchant_id, SUM(amount) AS total
FROM payment_ledger_cs
WHERE pay_time >= '2025-01-01' AND txn_status = 1
GROUP BY merchant_id;

Point queries (row-store index):

SELECT txn_status, amount, pay_time, pay_channel
FROM payment_ledger_cs
WHERE order_id = 10086001
ORDER BY pay_time DESC
LIMIT 5;

The primary table each column ensures the column-store scanning advantage for aggregate SQL; point queries are handled by the row-store index.

Example 3: Pure column-store table (wide-range analysis, weak TP)

The table is used only for batch processing such as end-of-day reconciliation and regulatory reporting, with no online queries by order number. It can accept the absence of a primary key to trade in for import performance. For more information, see Primary key design practices in AP scenarios.

CREATE TABLE payment_ledger_dw (
    txn_id       BIGINT,
    order_id     BIGINT,
    pay_time     DATETIME,
    pay_channel  VARCHAR(32),
    txn_status   TINYINT,
    amount       DECIMAL(18,2),
    merchant_id  BIGINT
)
WITH COLUMN GROUP(each column);

-- It is recommended to perform a major compaction and collect statistics after bulk import.

SELECT DATE(pay_time) AS dt,
       COUNT(*) AS txn_cnt,
       SUM(CASE WHEN txn_status = 1 THEN amount ELSE 0 END) AS succ_amt
FROM payment_ledger_dw
WHERE pay_time >= '2025-01-01' AND pay_time < '2025-02-01'
GROUP BY DATE(pay_time);

Example 4: Hybrid row-column store table

The business insists on using a single payment_ledger to handle both payment queries and operational analysis, and is willing to accept double baseline storage and higher major compaction costs.

CREATE TABLE payment_ledger_htap (
    txn_id       BIGINT        NOT NULL,
    order_id     BIGINT        NOT NULL,
    pay_time     DATETIME      NOT NULL,
    pay_channel  VARCHAR(32)   NOT NULL,
    txn_status   TINYINT       NOT NULL,
    amount       DECIMAL(18,2) NOT NULL,
    merchant_id  BIGINT        NOT NULL,
    PRIMARY KEY (order_id, pay_time, txn_id)
)
PARTITION BY HASH(order_id) PARTITIONS 64
WITH COLUMN GROUP(all columns, each column);

-- Point queries (optimizer favors row-based path)
SELECT amount, txn_status FROM payment_ledger_htap
WHERE order_id = 10086001 AND txn_status = 1
ORDER BY pay_time DESC LIMIT 1;

-- Analysis (optimizer prefers columnar storage path)
SELECT pay_channel, SUM(amount) FROM payment_ledger_htap
WHERE pay_time >= '2025-01-15' AND txn_status = 1
GROUP BY pay_channel;

OceanBase

Customer Stories

Documentation