List of Contents

This chapter explains how to create, alter and drop indexes.

There are 3 types indexes in Machbase.

  • A KEYWORD index is for searching string in the varchar type column of log table.
  • A LSM(Log Structured Merge) index is for the columns in log table. it's based on BITMAP index.
  • A REDBLACK index is for the column in volatile or lookup table.

if not specified index type, it's a LSM index for log table and a REDBLACK index for volatile/lookup table by default.
Machbase does not support composite index and you can create index for a single column only.



CREATE INDEX index_name ON table_name (column_name) [index_type] [tablespace] [index_prop_list]

index_type ::= INDEX_TYPE { LSM | REDBLACK | KEYWORD }

tablespace ::= TABLESPACE tablesapce_name

index_prop_list ::= value_pair, value_pair, ...

value_pair ::= property_name = property_value


mach> CREATE INDEX id_index ON log_data(id) INDEX_TYPE LSM TABLESPACE tbs_data MAX_LEVEL=3;
Created successfully.

LSM Index

LSM (Log Structured Merge) index is optimized for storing and searching big data. The partitions of LSM indexes will be maintained based on levels, and the partitions of lower level are merged and move up to the higher level. When the partitions of lower levels are merged for higher levels, the lower levels wil be deleted.
The bitmap index and keyword index of Machbase DB are based on LSM index. Building levels of index partitions are conducted by background threads.
When searching for data with the index, it has advantages as follows as a higher level of partition is created by merging a lower level of partitions.

  • When the key is duplicated, it is stored only once so that it can save disk space.
  • It can save costs for not opening and closing the entire files and reduce the number of index pages that try to access because it searches only one index partition, not multiple partitions.

Table 1. LSM index properties

Name Description
MAX_LEVEL (DEFAULT = 2, MIN = 0, MAX = 3 ) Currently the maximum value of LSM index levels is 3. The maximum number of records for a partition cannot exceed 200M. The partition size of each level is the number of values from the previous partitions * 10. For example, if MAX_LEVEL = 3 and PART_VALUE_COUNT = 100,000, the results will be "Level 0 = 100,000, Level 1 = 1000,0000, Level 2 = 10,000,000 and Level 3 = 100,000,000". If the partition size of the final level exceeds 200M, it fails to create indexes.
PAGE_SIZE (DEFAULT = 512 1024, MIN = 32 1024, MAX = 1 1024 1024) When the key value and bitmap value of the index are stored, the size of page will be specified as well. The default size is 512K.
BLOOM_FILTER (DEFAULT = 1, DISABLE = 0, ENABLE(DEFAULT) = non_zero_integer) It specifies whether to set BLOOM FILTER on indexes. If it is set to BLOOM FILTER, it can search values which are not existed in the index, but it increases the time for creating indexes. If it uses RANGE condition only, BLOOM FILTER is no longer used so that it doesn't have to be created. When BLOOM FILTER is not created, BLOOM_FILTER needs to be set as 0.
BITMAP_ENCODE(DEFAULT = EQUAL, RANGE) It sets bitmap types of an index. For BITMAP_ENCODE=EQUAL(default value), it creates the same value against bitmap. The value is same as the key value. If it is for BITMAP = RANGE, it creates bitmap based on the range of key value. When "=" is used for query condition, it is better to set BITMAP_ENCODE=EQUAL, and if a specific range value is used for query condition, it is better to set as BITMAP_ENCODE=RANGE. In the case of BITMAP=RANGE, the generation costs will be a bit higher than EQUAL.


It is a memory index for data analysis in real-time and can only be created in the volatile and lookup table. Also, it can be created for all the columns in the volatile and lookup tables but can be created for a single column at a time.


It is only for text search and it can be created for varchar and text columns in the log table.
However, it must be created for a single column only.

Default Index Type

Index types can be specified in the index_type clause.
If no index type is specified, the default index type is set based on the table type.

Table 2. Default index type

type of table Default index type
Volatile table REDBLACK
Log table LSM

Index Property

Index partition property is specified when creating an index with CREATE INDEX syntax. By default, it is set to 100,000. If you need to change this value, it can be changed via PART_VALUE_COUNT property which indicates a number of row that is stored in a partition of index. For more detailed information, please refer to the Setup Partition Size.


mach> CREATE INDEX id_index ON log_data(id) TABLESPACE tbs_data MAX_LEVEL=3, PART_VALUE_COUNT = 200000;
Created successfully.


ALTER INDEX statement modifies the property of a specified index. a KEY_COMPRESS property determines where to compress key values of the index or not. The default value is 1. It can set a bitmap index only.


ALTER INDEX index_name SET KEY_COMPRESS = { 0 | 1 }




DROP INDEX statement drops the specified index. When there is another session searching for the table, an error occurs and fail to drop the table.


DROP INDEX index_name;


mach>DROP INDEX id_index;
Dropped successfully.

results matching ""

    No results matching ""