Configure Skip Index in conjunction with table update mode

Starting from V4.3.0, columnstore tables support the automatic generation of Skip Indexes for baseline data (columnstore tables default to creating MIN_MAX type Skip Indexes for columns). Starting from V4.6.0, Skip Indexes can also be generated for incremental data.

Skip Indexes record aggregated information for columns at the micro-block level in SSTables, such as minimum and maximum values, number of null values, and optional SUM information. This allows queries to filter out data blocks that do not meet the conditions before scanning, thereby reducing unnecessary I/O and computational overhead. For more information about Skip Indexes, see Column Skip Index Properties (MySQL Mode). In analytical query scenarios, Skip Indexes are particularly effective for large table scans and queries with range or equality conditions, significantly reducing the amount of data scanned. In scenarios with large data volumes and highly selective filter conditions, the optimization benefits are typically more pronounced.

For columnstore tables, baseline data usually has the necessary aggregated information. However, if Skip Indexes for incremental data are not generated for a long time, new data writes may still trigger a significant number of unnecessary micro-block scans during queries, which can impact analytical query performance.

Therefore, in AP scenarios, it is recommended to configure parameters such as table update mode (MERGE_ENGINE) and skip_index_level based on business characteristics. This ensures that incremental data can also generate Skip Indexes when conditions are met, thereby improving the execution efficiency of queries with filter conditions.

Default mode in AP scenarios

The value of skip index for incremental user columns depends on the combination of the table-level MERGE_ENGINE and skip_index_level. Tenant-level configurations only affect tables created after this configuration is set, and only if the corresponding option is not explicitly specified in the CREATE TABLE statement. The actual definition of the table is determined by the SHOW CREATE TABLE command.

Please verify the current values for the following parameters:

• default_table_merge_engine (tenant-level)

  • Query: SHOW PARAMETERS LIKE 'default_table_merge_engine';
  • Default: PARTIAL_UPDATE. AP Recommendation: Change it to delete_insert or append_only, or explicitly specify MERGE_ENGINE in the CREATE TABLE DDL.
  • If not set: ALTER SYSTEM SET default_table_merge_engine = 'delete_insert'; (or 'append_only'). This only affects tables created after this change, and only if the DDL does not specify MERGE_ENGINE.

• default_skip_index_level (tenant-level)

  • Query: SHOW PARAMETERS LIKE 'default_skip_index_level';
  • Default: 1 in AP scenarios. If you specify skip_index_level at the table level, it will inherit the value of default_skip_index_level.
  • If not set: ALTER SYSTEM SET default_skip_index_level = 1; (or 0). This only affects tables created after this change, and only if the DDL does not specify skip_index_level.

• Table-level MERGE_ENGINE (table update mode)

  • Query: SHOW CREATE TABLE dbname.tblname;
  • To enable incremental skip index for user columns: The value must be DELETE_INSERT or APPEND_ONLY (not PARTIAL_UPDATE).
  • If not set: You cannot change the table mode using ALTER TABLE. Instead, you must create a new table and specify MERGE_ENGINE, then migrate the data. When creating a new table, you can also explicitly specify MERGE_ENGINE in the DDL, without relying on the tenant's default.

• Table-level skip_index_level

  • Query: SHOW CREATE TABLE dbname.tblname; or the SKIP_INDEX_LEVEL view (DBA_TABLES / CDB_TABLES).
  • To enable incremental skip index: The value must be 1, and it must also meet the previous table mode requirement.
  • If not set: If the table mode is already DELETE_INSERT or APPEND_ONLY, execute ALTER TABLE tbl SET skip_index_level = 1; (or 0). This change will take effect during the next major compaction or minor compaction.

To create a new table with minimal reliance on tenant defaults: Explicitly specify MERGE_ENGINE = delete_insert (or append_only) and skip_index_level = 1 in the CREATE TABLE statement. For columnar analysis, you can also add WITH COLUMN GROUP(each column). See the "Syntax examples" section below.

For more information about the configuration items, see default_table_merge_engine and default_skip_index_level.

Syntax examples

-- Specify when creating a table
CREATE TABLE logs (
  event_time TIMESTAMP,
  level VARCHAR(10)
)
MERGE_ENGINE = DELETE_INSERT
skip_index_level = 1;

-- Modify an existing table (only skip_index_level can be modified; MERGE_ENGINE cannot be modified)
ALTER TABLE logs SET skip_index_level = 1;

Technical analysis

For tenants and tables, see Default mode in AP scenarios for details on what to check and how to modify. This section describes the baseline and incremental differences and the meaning of the skip_index_level parameter from the storage layer.

Skip Index generation for baseline data

• Support: OceanBase Database supports generating Skip Indexes for baseline data (Major SSTables). For columnstore tables, MIN_MAX type Skip Indexes are generated by default for each column starting from V4.3. For rowstore tables, you must explicitly specify SKIP_INDEX(MIN_MAX) or SKIP_INDEX(SUM) on each column to generate Skip Indexes for baseline data during major compaction.
• Generation conditions: For columnstore tables, Skip Indexes are automatically generated during major compaction based on engine strategies (default MIN_MAX, with SUM added based on column attributes). For rowstore tables, Skip Indexes are generated only when the column has the Skip Index attribute during minor compaction. This is not directly related to the table update mode (MERGE_ENGINE).

Skip Index generation for incremental data

• Support: The generation of Skip Indexes for incremental data (Memtable, Mini/Minor SSTables) is strongly related to the table update mode. Skip Indexes can only be safely generated for user columns in incremental data when the records are complete rows.
• Generation conditions (all must be met):
  1. Table mode: MERGE_ENGINE must be set to DELETE_INSERT or APPEND_ONLY.
  2. Table-level configuration: skip_index_level = 1 (specified at table creation or inherited from the tenant's default_skip_index_level = 1).
  3. Effective timing: After the next Mini Compaction (minor compaction), incremental data will include Skip Indexes.

skip_index_level parameter (table level)

If you do not explicitly specify skip_index_level when creating a table, the value is inherited from the tenant's default_skip_index_level parameter. After modifying skip_index_level, the new configuration takes effect during the next minor compaction.

Applicability

Not applicable or notes

• PARTIAL_UPDATE tables: Skip Indexes for incremental user columns are not generated; even if you set skip_index_level = 1, it will be ignored. Baselines are still generated as columnar or rowar with schema.
• Column type and attribute limitations: Skip Index aggregates are not generated for the following columns: virtual generated columns; JSON and spatial (geo) types; and outrow LOBs.
• Small tables or infrequent AP queries: The storage and compaction overhead of Skip Indexes may outweigh the benefits. You can use skip_index_level = 0 or avoid relying on incremental Skip Indexes as needed.
• High-concurrency update TP tables (PARTIAL_UPDATE): If you expect to use incremental user column Skip Indexes for analysis filtering, the system will not generate them. Avoid optimizing based on AP incremental Skip Index expectations.

Recommended (AP)

• Columnar analysis tables: Use WITH COLUMN GROUP(each column), along with MERGE_ENGINE = DELETE_INSERT and skip_index_level = 1, to benefit both baselines and increments from Skip Indexes.
• Log / metric append writes: Use MERGE_ENGINE = APPEND_ONLY and, if needed, set skip_index_level = 1.

Example: Configure the table update mode and Skip Index for an AP columnstore table

Scenario

• Table usage: Store business events or metrics details in a columnstore table, and perform range queries and aggregations based on columns such as event_time.
• Objective: Set WITH COLUMN GROUP(each column), MERGE_ENGINE = DELETE_INSERT, and skip_index_level = 1. Optionally, explicitly specify SKIP_INDEX(MIN_MAX, SUM) for some columns.

Step 1: View the current tenant-level parameters

Execute the following statements in the sys tenant (or in the specified tenant's sys tenant) to confirm the current default table update mode and Skip Index level:

-- View the default table update mode (usually partial_update by default)
SHOW PARAMETERS LIKE 'default_table_merge_engine';

-- View the default skip_index_level for new tables (usually 1 in AP scenarios)
SHOW PARAMETERS LIKE 'default_skip_index_level';

If your tenant primarily uses AP, ensure the tenant is configured as follows (note that default_skip_index_level is commonly already set to 1, so focus mainly on default_table_merge_engine):

ALTER SYSTEM SET default_table_merge_engine = 'delete_insert';
-- If it's not 1, execute: ALTER SYSTEM SET default_skip_index_level = 1;

The changes will only take effect for tables created afterward and will not affect existing tables.

Step 2: Specify the table update mode and Skip Index level when creating a table

Explicitly specify WITH COLUMN GROUP(each column), MERGE_ENGINE = delete_insert, and skip_index_level = 1 when creating a table. Optionally, specify the Skip Index attribute for columns:

CREATE TABLE ap_logs (
  log_id     BIGINT,
  event_time TIMESTAMP,
  level      VARCHAR(32),
  duration_ms INT,
  extra      VARCHAR(1024),
  c3         FLOAT SKIP_INDEX(MIN_MAX, SUM),
  PRIMARY KEY (log_id)
) MERGE_ENGINE = delete_insert
  skip_index_level = 1
  WITH COLUMN GROUP(each column);

Step 3: Modify the Skip Index for an existing table

If the table already exists and is set to DELETE_INSERT or APPEND_ONLY, execute the following to enable incremental Skip Index generation:

ALTER TABLE ap_logs SET skip_index_level = 1;

References

• Skip index properties (MySQL mode)
• Create a table (MySQL mode) — Specify the update model of a table
• default_table_merge_engine
• default_skip_index_level
• DBA_TABLES (MySQL mode)
• CDB_TABLES (sys tenant)
• DBA_TABLES (Oracle mode)