INDEX

This reference explains index syntax and support by table type. Indexes reduce query cost but require maintenance during ingestion and changes. Add them after checking actual predicates and execution plans.

CREATE INDEX

Supported since Machbase 8.7.0

create_index_stmt ::=
    'CREATE' index_modifier? 'INDEX' [ 'IF NOT EXISTS' ] index_name
    'ON' index_target '(' index_column_list ')'
    [ 'INDEX_TYPE' ( 'LSM' | 'KEYWORD' | 'BITMAP' | 'REDBLACK' | 'TAG' ) ]
    [ 'TABLESPACE' tablespace_name ]
    [ index_property_list ]

index_modifier ::= 'UNIQUE' | 'PRIMARY KEY'

index_target ::= table_name | table_name 'METADATA'

index_column_list ::=
    column_name ( ',' column_name )*
  | column_name json_path

index_property_list ::=
    ( 'MAX_LEVEL'        '=' number
    | 'PAGE_SIZE'        '=' number
    | 'BITMAP_ENCODE'    '=' ( 'EQUAL' | 'RANGE' )
    | 'PART_VALUE_COUNT' '=' number )
    ( ',' index_property_list )*

IF NOT EXISTS

IF NOT EXISTS succeeds without error and retains the existing index if the same index name exists for the same database and owner.

• If the name does not exist, validate table, column, index type, properties, and privileges as for ordinary CREATE INDEX, then create it.
• If the name exists, do not compare or change table, column, index type, JSON path, or properties.
• The duplicate namespace is database + owner + index name. The same name in another database or owner is a different index.
• Without the option, CREATE INDEX returns the existing duplicate-name error.

CREATE LOG TABLE sensor_log_ifne (
    sensor_id INTEGER,
    value     DOUBLE
);

CREATE INDEX IF NOT EXISTS sensor_log_ifne_idx
    ON sensor_log_ifne(sensor_id);

-- Existing name: succeeds and retains the original SENSOR_ID mapping.
CREATE INDEX IF NOT EXISTS sensor_log_ifne_idx
    ON sensor_log_ifne(value);

SHOW INDEX sensor_log_ifne_idx;

DROP TABLE sensor_log_ifne;

Supported Conditional Creation Forms

Deprecated CREATE BITMAP INDEX, CREATE KEYWORD INDEX, and CREATE REDBLACK INDEX forms do not accept IF NOT EXISTS. Use ordinary syntax.

CREATE INDEX IF NOT EXISTS idx_message
    ON app_log(message) INDEX_TYPE KEYWORD;

Support by Table Type

The default internal index depends on table type. Specifying an index type from another table type does not necessarily create the same structure.

LOG Indexes

CREATE INDEX idx_ts ON sensor_log (ts);
CREATE INDEX idx_msg ON app_log (message) INDEX_TYPE KEYWORD;
CREATE INDEX idx_status ON sensor_log (status)
    INDEX_TYPE BITMAP BITMAP_ENCODE = RANGE;

Choose properties such as LSM MAX_LEVEL/PAGE_SIZE and BITMAP BITMAP_ENCODE through measurements of data distribution and query predicates.

TAG Indexes

Default access structures for TAG names and the time axis are managed automatically. Consider TAG/KV secondary indexes when value columns are frequently used as standalone predicates.

CREATE INDEX idx_value ON sensor_tag (value) INDEX_TYPE TAG;

JSON value columns support indexes by path.

CREATE INDEX idx_sensor ON tag_json (value.sensor.name);
CREATE INDEX idx_metric ON tag_json (value->'$.metric');
CREATE INDEX idx_item ON tag_json (value.items[0]."product-id");

Ordinary TAG METADATA columns are indexed automatically. Use the following form to add a path from a METADATA JSON column.

CREATE INDEX idx_ship_owner
ON ships METADATA (info->'$.owner');

For support scope and execution-plan examples, see TAG Indexes and Performance.

TRANSACTION Indexes

TRANSACTION supports PRIMARY KEY, UNIQUE INDEX, and ordinary single/composite indexes.

CREATE PRIMARY KEY INDEX idx_pk_order ON orders (order_id);
CREATE UNIQUE INDEX uidx_account_email ON account (email);
CREATE UNIQUE INDEX uidx_tenant_login ON account (tenant_id, login_name);
CREATE INDEX idx_category_name ON product (category, product_name);

A table permits one single-column PRIMARY KEY. CREATE UNIQUE INDEX supports composite columns; NULL-containing keys are not duplicates of other NULL-containing keys. See TRANSACTION Indexes and Performance.

VOLATILE and LOOKUP Indexes

VOLATILE and LOOKUP use REDBLACK in-memory indexes.

CREATE INDEX idx_status ON device_status (status) INDEX_TYPE REDBLACK;

For primary key and secondary-index design by type, see:

• VOLATILE Indexes and Performance
• LOOKUP Indexes and Performance

DROP INDEX

drop_index_stmt ::= 'DROP INDEX' index_name

DROP INDEX idx_status;

Deletion can fail while sessions use the target index. Check execution plans and production queries using it before dropping it.

Related Documentation

• SEARCH / ESEARCH / REGEXP
• Query Performance Tuning