MaxCompute:Bitmap index (Beta)

When to use bitmap indexes

Bitmap indexes work best for:

• Low-cardinality columns with a limited number of distinct values, such as gender or city

• Logical operations (AND, OR)—bitmap indexes perform efficient bitwise operations, making them well-suited for multidimensional queries

When not to use bitmap indexes

Avoid creating bitmap indexes for:

• High-cardinality columns with many distinct values, such as ID card numbers or phone numbers. When too many distinct values exist or filtering is ineffective, index storage overhead increases and read performance degrades.

• Frequently updated columns. Frequent updates to indexed columns increase storage overhead and degrade read performance.

Supported column types and operators

Bitmap indexes can only be created on non-nested columns of these types: STRING, CHAR, VARCHAR, TINYINT, SMALLINT, INT, BIGINT, and DOUBLE.

Once created, bitmap indexes accelerate queries that use these operators:

Billing

• Storage: Bitmap indexes consume additional storage space, calculated by actual index size. This storage is consolidated with your project data and billed at the standard storage rate.

• Computing: Index creation triggers additional compute jobs.

  • Subscription: Jobs use the subscription resources of the project.

  • Pay-as-you-go: Cost = Unit price of pay-as-you-go SQL jobs × Complexity 1 × Input data amount of index-related jobs

Prerequisites

Before creating a bitmap index, enable schema evolution on the project:

setproject odps.schema.evolution.enable=true;

Usage notes

• Create a bitmap index for one column at a time. To index multiple columns on the same table, run the creation statement once per column.

• Data inserted after index creation is automatically covered by the bitmap index.

• To apply a bitmap index to existing data, rebuild the index after creation.

Sample data

The examples in this topic use two tables: emp (non-partitioned) and sale_detail (partitioned).

Create a bitmap index

Syntax

CREATE BITMAP INDEX <index_name> ON TABLE <table_name> FOR COLUMNS(<col_name>) [COMMENT 'indexcomment'];

Parameters

Examples

Create the empno_index bitmap index on the empno column of the emp table:

CREATE BITMAP INDEX empno_index
ON TABLE emp
FOR COLUMNS(empno)
COMMENT 'idxcomment';

Create the job_index bitmap index on the job column of the emp table:

CREATE BITMAP INDEX job_index
ON TABLE emp
FOR COLUMNS(job)
COMMENT 'idxcomment';

Create the shop_name_index bitmap index on the shop_name column of the sale_detail table:

CREATE BITMAP INDEX shop_name_index
ON TABLE sale_detail
FOR COLUMNS(shop_name)
COMMENT 'indexcomment';

Rebuild a bitmap index

Rebuild a bitmap index to apply it to existing data that was present before the index was created.

Syntax

For non-partitioned tables:

ALTER TABLE <table_name> REBUILD BITMAP INDEX;

For partitioned tables:

ALTER TABLE <table_name> PARTITION
(<partition_name1=value1>[, partition_name2=value2, ...]) REBUILD BITMAP INDEX;

For partitioned tables, rebuild one partition at a time.

Parameters

Examples

Rebuild a bitmap index for the non-partitioned emp table:

ALTER TABLE emp REBUILD BITMAP INDEX;

Rebuild a bitmap index for a specific partition of the sale_detail table:

ALTER TABLE sale_detail PARTITION (sale_date='2023', region='china') REBUILD BITMAP INDEX;

List bitmap indexes

Syntax

SHOW INDEXES ON <table_name>;

Example

-- List indexes on the emp table
SHOW INDEXES ON emp;

The result is returned as JSON:

{"Indexes": [{
            "id": "00c84b0e9e6e4097bdfe3a01b91848ac",
            "indexColumns": [{"name": "job"}],
            "name": "job_index",
            "properties": {"comment": "jobidx"},
            "type": "BITMAP"},
           {
            "id": "18a9755c7a8a4182a6b51165e786aa62",
            "indexColumns": [{"name": "empno"}],
            "name": "empno_index",
            "properties": {"comment": "idxcomment"},
            "type": "BITMAP"}]}

Query data with bitmap indexes

The following examples show point and range queries on the emp table with bitmap indexes applied.

Point query — filter by the job column:

SELECT * FROM emp WHERE job = 'clerk';

Result:

+------------+-------+-----+------------+------------+-----+
| empno      | ename | job | mgr        | hiredate   | sex |
+------------+-------+-----+------------+------------+-----+
| 7369       | smith | clerk | 7902       | NULL       | Male |
| 7876       | adams | clerk | 7788       | NULL       | Female |
| 7900       | james | clerk | 7698       | NULL       | Male |
| 7934       | miller | clerk | 7782       | NULL       | Female |
+------------+-------+-----+------------+------------+-----+

Range query — filter by a range of empno values:

SELECT * FROM emp WHERE empno BETWEEN 7300 AND 7800;

Result:

+------------+------------+------------+------------+------------+
| empno      | ename      | job        | mgr        | sex        |
+------------+------------+------------+------------+------------+
| 7369       | smith      | clerk      | 7902       | Male       |
| 7499       | allen      | salesman   | 7698       | Female     |
| 7521       | ward       | salesman   | 7698       | Male       |
| 7654       | martin     | salesman   | 7698       | Male       |
| 7698       | blake      | manager    | 7839       | Male       |
| 7782       | clark      | manager    | 7839       | Male       |
| 7788       | scott      | analyst    | 7566       | Male       |
+------------+------------+------------+------------+------------+

Verify that bitmap indexes are filtering data

After running a query, check the filtering effect in the operational logs:

1. In the console, click the LogView URL in the operational logs to open the LogView page.

   

2. On the Json Summary tab, search for the bitmap keyword. The results show the number of rows filtered by bitmap indexes and the additional time consumed for creating bitmap indexes. I/O operation time is not included in this measurement.

   

Drop a bitmap index

Syntax

DROP INDEX [IF EXISTS] <index_name> ON TABLE <table_name>;

Parameters

Example

DROP INDEX IF EXISTS job_index ON TABLE emp;