Bitmap index (Beta) - MaxCompute - Alibaba Cloud Documentation Center

Background

All four types improve query performance by filtering data. While a range-clustered index requires adherence to the leftmost matching principle for range filtering and a Bloom filter index is best for point queries, a bitmap index is designed primarily for range queries. In range filtering scenarios, a bitmap index can significantly accelerate queries by filtering out over 60% of the data.

A bitmap index offers the following advantages:

Bitmap index queries are not restricted by the leftmost matching principle.
You can create bitmap indexes on multiple columns of a table. These indexes can be combined with other index types to accelerate queries.
A bitmap index provides effective, row-level filtering by creating a bitmap for each unique value.
It is suitable for set operations, such as OR and AND, and can optimize multidimensional queries.

Use cases

Columns with low cardinality (a limited number of distinct values), such as those for gender or city.
Logical operations, such as AND and OR. Bitmap indexes enable efficient bitwise operations on bitmaps.

Note

Using bitmap indexes on high-cardinality columns increases storage consumption and degrades read performance. Therefore, avoid creating a bitmap index on the following types of columns:

Columns with low data duplication, such as ID card or phone numbers.
Columns that are frequently updated.

Billing

Storage: A bitmap index consumes additional storage space. This space is metered by the index size, added to your project's total data storage, and billed at the standard storage rate.
Compute: Building an index consumes computing resources. For subscription projects, these tasks use your allocated resources. For pay-as-you-go projects, you are charged for these tasks based on the following formula: Cost = Unit price for pay-as-you-go SQL × Complexity 1 × Input data size of the index-related task.

Limitations

A bitmap index can be created only on non-nested columns of the following data types: STRING, CHAR, VARCHAR, TINYINT, SMALLINT, INT, BIGINT, and DOUBLE.
Bitmap indexes accelerate queries that use the following operators: <=, <, =, >, >=, IN, BETWEEN, and IS NULL.

Usage notes

Before you create a bitmap index, run the setproject odps.schema.evolution.enable=true; command to enable schema evolution.
You can create a bitmap index for only one column at a time. To create bitmap indexes for multiple columns in a table, you must run the creation statement for each column.
After an index is created, the system automatically maintains it for newly inserted data.
To index existing data, you must rebuild the index.

Sample data

Note

The syntax examples for the bitmap index are based on data in the emp and sale_detail tables.

Non-partitioned table

Create the emp table.

CREATE TABLE IF NOT EXISTS emp( 
  empno BIGINT, 
  ename STRING, 
  job STRING, 
  mgr BIGINT, 
  sex STRING 
);

Insert data.

INSERT INTO emp(empno,ename,job,mgr,sex) VALUES  
(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'),
(7839,'king','president',NULL,'Male'),
(7844,'turner','salesman',7698,'Female'),
(7876,'adams','clerk',7788,'Female'),
(7900,'james','clerk',7698,'Male'),
(7902,'ford','analyst',7566,'Male'),
(7934,'miller','clerk',7782,'Female');

Partitioned table

Create the sale_detail partitioned table.

CREATE TABLE IF NOT EXISTS sale_detail(
 shop_name     STRING,
 customer_id   STRING,
 total_price   DOUBLE)
PARTITIONED BY (sale_date STRING, region STRING);

Add partitions.

ALTER TABLE sale_detail ADD PARTITION (sale_date='2023', region='china') PARTITION (sale_date='2024', region='shanghai');

Create a bitmap index

Syntax

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

Parameters

index_name: Required. The name of the index.
table_name: Required. The name of the table.
col_name: Required. The name of the column to index.

Examples

Create an index named empno_index for the empno column of the emp table.

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

Create an index named job_index for the job column of the emp table.

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

Create an index named shop_name_index for 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

Syntax

To rebuild the index for existing data, use the following commands.

Rebuild an index on a non-partitioned table.

ALTER TABLE <table_name> REBUILD BITMAP INDEX;

Rebuild an index on a partitioned table.
```
ALTER TABLE <table_name> PARTITION 
(<partition_name1=value1>[, partition_name2=value2, ...]) REBUILD BITMAP INDEX;
```
Note
For a partitioned table, you can rebuild the index for only one partition at a time.

Parameters

Non-partitioned table

table_name: Required. The name of the table.
Partitioned table
- table_name: Required. The name of the table.
- partition_name: Required. The name of the partition.
- value: Required. The value of the partition.

Examples

Example 1: Non-partitioned table.
```
ALTER TABLE emp REBUILD BITMAP INDEX;
```

Example 2: Partitioned table.

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

List bitmap indexes

Syntax

SHOW INDEXES ON <table_name>;

Parameters

table_name: Required. The name of the table.

Example

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

The result is returned in JSON format.

{"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 and check index performance

Examples

Example 1: Perform a point query on 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 |
+------------+-------+-----+------------+------------+-----+

Example 2: Perform a range query on the empno column.

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       | 
+------------+------------+------------+------------+------------+

Check the index performance.
1. In the console's operational logs, click the LogView link to open the LogView page.
```
2024-11-05 16:46:42 start to get jobId:
2024-11-05 16:46:42 get jobId:20241105084642689g10x9kj2im1
ID = 20241105084642689g10x9kj2im1
Log view:
http://logview.odps.aliyun.com/logview/?h=http://service.cn
i01t7IkFjd6lvbI16wyJvZHBz0J1JYhQiXSwIRuZmW0N1oiQwsb3ciLC
Job Queueing...
Summary:
resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min
```
2. On the LogView page, search for the "Bitmap" keyword on the Json Summary tab. You can view the index's filtering effect and its build latency (excluding I/O time).
  
  Key metrics include BitmapIndexFilteredRowCount (the number of rows filtered), BitmapIndexLatency (build latency), and BitmapIndexInBytes and BitmapIndexOutBytes (input and output bytes).

Drop a bitmap index

Syntax

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

Parameters

index_name: Required. The name of the index.
table_name: Required. The name of the table.

Example

DROP INDEX IF EXISTS job_index ON TABLE emp;