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 todelete_insertorappend_only, or explicitly specifyMERGE_ENGINEin 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 specifyMERGE_ENGINE.
- Query:
default_skip_index_level (tenant-level)
- Query:
SHOW PARAMETERS LIKE 'default_skip_index_level'; - Default:
1in AP scenarios. If you specifyskip_index_levelat the table level, it will inherit the value ofdefault_skip_index_level. - If not set:
ALTER SYSTEM SET default_skip_index_level = 1;(or0). This only affects tables created after this change, and only if the DDL does not specifyskip_index_level.
- Query:
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_INSERTorAPPEND_ONLY(notPARTIAL_UPDATE). - If not set: You cannot change the table mode using
ALTER TABLE. Instead, you must create a new table and specifyMERGE_ENGINE, then migrate the data. When creating a new table, you can also explicitly specifyMERGE_ENGINEin the DDL, without relying on the tenant's default.
- Query:
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_INSERTorAPPEND_ONLY, executeALTER TABLE tbl SET skip_index_level = 1;(or0). This change will take effect during the next major compaction or minor compaction.
- Query:
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;
Note
If MERGE_ENGINE or skip_index_level is not specified in the DDL statement for creating a table, the values are inherited from the tenant-level default_table_merge_engine and default_skip_index_level parameters, respectively. The ALTER TABLE ... SET skip_index_level statement only modifies the table-level switch and cannot modify MERGE_ENGINE.
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)orSKIP_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):
- Table mode:
MERGE_ENGINEmust be set toDELETE_INSERTorAPPEND_ONLY. - Table-level configuration:
skip_index_level = 1(specified at table creation or inherited from the tenant'sdefault_skip_index_level = 1). - Effective timing: After the next Mini Compaction (minor compaction), incremental data will include Skip Indexes.
- Table mode:
Note
For PARTIAL_UPDATE tables, only the changed columns are recorded. Therefore, incremental data cannot reliably obtain complete rows for min/max aggregation information. Even if skip_index_level = 1 is set, the system will not generate Skip Indexes for incremental user columns.
skip_index_level parameter (table level)
Value |
Description |
|---|---|
0 |
Skip Indexes are generated for columnstore baseline data based on engine strategies and for rowstore baseline data based on schema column attributes. No Skip Indexes are generated for incremental SSTables. |
1 |
Skip Indexes are generated for baseline data in the same way as when skip_index_level = 0. For incremental SSTables, Skip Indexes are generated in the same way as for baseline data when the table mode is DELETE_INSERT or APPEND_ONLY. For columnstore tables, Skip Indexes are generated based on priority and space limitations. For rowstore tables, Skip Indexes are generated based on schema. New tables without an explicit skip_index_level setting inherit the tenant's default_skip_index_level (usually 1, based on actual parameters). |
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 = 0or 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 withMERGE_ENGINE = DELETE_INSERTandskip_index_level = 1, to benefit both baselines and increments from Skip Indexes. - Log / metric append writes: Use
MERGE_ENGINE = APPEND_ONLYand, if needed, setskip_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, andskip_index_level = 1. Optionally, explicitly specifySKIP_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);
Note
WITH COLUMN GROUP(each column): Creates a columnstore table. By default, the baseline generates MIN_MAX and other Skip Indexes for each column. Incremental data will also generate Skip Indexes following the same strategy when the table mode andskip_index_levelare met.MERGE_ENGINE = delete_insert: Enables full-column updates, allowing safe Skip Index generation for incremental data.skip_index_level = 1: Incremental SSTables of this table will generate Skip Indexes following the baseline strategy.c3 FLOAT SKIP_INDEX(MIN_MAX, SUM): Specifies the Skip Index type (MIN_MAX + SUM) for this column in the schema. This type will be prioritized in sorting.
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;
Note
The new configuration will take effect for incremental data after the next major compaction or minor compaction. The MERGE_ENGINE parameter cannot be modified using ALTER TABLE. Only when the table is set to DELETE_INSERT or APPEND_ONLY will skip_index_level = 1 generate Skip Indexes for incremental data; it will not take effect for PARTIAL_UPDATE tables. For more information about the tenant-level default value, see default_skip_index_level.