Help Center/ Data Warehouse Service / SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ Functions and Operators/ Roaring Bitmap Functions and Operators/ Roaring Bitmap Operators
Updated on 2026-01-06 GMT+08:00
View PDF
Share

Roaring Bitmap Operators

Starting with version 8.1.3, DWS introduces efficient bitmap operators. They can handle set operations like intersection, union, difference, and XOR quickly in databases. They boost performance for complex tasks and suit frequent set operation needs, such as feature engineering in data mining and machine learning, and scenarios where quick data filtering and aggregation are required.

  • In clusters of version 9.1.1.100 or later, 64-bit integer bitmaps are supported.
  • For clusters running version 9.1.1.100, if you use the |, -, @>, or <@ bitmap operators with bigint inputs, you cannot directly use nvarchar2 as the bigint input. Instead, convert the data type using ::bigint. Example:
select '2022'::nvarchar2::bigint | '\x3a300000010000000000020010000000010002000300'::roaringbitmap;
                      ?column?                      
----------------------------------------------------
 \x3a300000010000000000030010000000010002000300e607
  • In clusters of version 9.1.1.200 or later, the nvarchar2 type can be directly used as the input of bigint. No forcible conversion is required.
Table 1 Roaring bitmap operators

Operator

Description

Return Type

Example

=

Compares two roaring bitmaps to check whether they are equal.

bool

Check that the two bitmaps are the same, so t is returned, indicating that they are equal.

1
2
3
4
5
 
SELECT rb_build('{1,2,3}') = rb_build('{1,2,3}');
?column?
----------
t
(1 row)

Check that the two bitmaps contain different elements, so f is returned, indicating that they are not equal.

1
2
3
4
5
 
SELECT rb_build('{2,3}') = rb_build('{1,2,3}');
?column?
----------
f
(1 row)

<>

Compares two roaring bitmaps to check whether they are unequal.

bool

Check that the two bitmaps are the same, so f is returned, indicating that they are equal.

1
2
3
4
5
 
SELECT rb_build('{1,2,3}') <> rb_build('{1,2,3}');
?column?
----------
f
(1 row)

Check that the two bitmaps contain different elements, so t is returned, indicating that they are not equal.

1
2
3
4
5
 
SELECT rb_build('{2,3}') <> rb_build('{1,2,3}');
?column?
----------
t
(1 row)

&

Calculates the intersection of two roaring bitmaps.

roaringbitmap

Return the intersection of the two bitmaps, that is, {2,3}.

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3}') & rb_build('{1,2,3}'));
rb_to_array
-------------
{2,3}
(1 row)

|

Calculates the union of two roaring bitmaps.

roaringbitmap

Return all unique elements {1,2,3}.

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3}') | rb_build('{1,2,3}'));
rb_to_array
-------------
{1,2,3}
(1 row)

|

Calculates the result of adding an element to a roaring bitmap.

roaringbitmap

Calculate the result of adding element 4 to a bitmap that contains elements {2,3} and return {2,3,4}.

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3}') | 4);
rb_to_array
-------------
{2,3,4}
(1 row)

#

Calculates the XOR of two roaring bitmaps.

roaringbitmap

Calculate the XOR of two bitmaps and return the element {1} that is different in the two sets.

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3}') # rb_build('{1,2,3}'));
rb_to_array
-------------
{1}
(1 row)

-

Calculates the result set in the first roaring bitmap but not in the second roaring bitmap.

roaringbitmap

Calculate the elements that belong to the first bitmap {2,3,4} but not in the second bitmap {1,2,3}, and return the element {4}. (It indicates that 4 exists only in the first bitmap.)

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3,4}') - rb_build('{1,2,3}'));
rb_to_array
-------------
{4}
(1 row)

-

Calculates the result set of removing a specified ID from a roaring bitmap.

roaringbitmap

Remove the specified element 3 from the bitmap {2,3,4} and return the result set {2,4}.

1
2
3
4
5
 
SELECT rb_to_array(rb_build('{2,3,4}') - 3);
rb_to_array
-------------
{2,4}
(1 row)

@>

Determines whether the roaring bitmap before an operator contains the roaring bitmap after the operator.

bool

Determine whether the bitmap {2,3,4} before the operator @> contains the bitmap {2,3} after the operator, and return t. It indicates that the bitmap {2,3,4} contains the bitmap {2,3}.

1
2
3
4
5
 
SELECT rb_build('{2,3,4}') @> rb_build('{2,3}');
?column?
----------
t
(1 row)

Determine whether the bitmap {2,3,4} before the operator @> contains the bitmap {5} after the operator, and return f. It indicates that the bitmap {2,3,4} does not contain the bitmap {5}.

1
2
3
4
5
 
SELECT rb_build('{2,3,4}') @> 5;
?column?
----------
f
(1 row)

<@

Determines whether the roaring bitmap before an operator is contained in the roaring bitmap after the operator.

bool

Determine whether the bitmap {4} before the operator <@ is contained in the bitmap {2,3,4} after the operator, and return t. It indicates that the bitmap {4} is contained.

1
2
3
4
5
 
SELECT  4 <@ rb_build('{2,3,4}');
?column?
----------
t
(1 row)

Determine whether the bitmap {2,3,4} before the operator <@ is contained in the bitmap {2,3} after the operator, and return f. It indicates that the bitmap {2,3,4} is not contained.

1
2
3
4
5
 
SELECT rb_build('{2,3,4}') <@ rb_build('{2,3}');
?column?
----------
f
(1 row)

&&

Determines whether two bitmaps overlap. If yes, true is returned. If no, false is returned.

bool

Determine whether two bitmaps overlap and return t. It indicates that the intersection {2,3} exists.

1
2
3
4
5
 
SELECT rb_build('{2,3,4}') && rb_build('{2,3}');
?column?
----------
t
(1 row)

Determine whether two bitmaps overlap and return f. It indicates that no intersection exists.

1
2
3
4
5
 
SELECT rb_build('{2,3,4}') && rb_build('{7,8,9}');
?column?
----------
f
(1 row)
Parent Topic: Roaring Bitmap Functions and Operators