Create Roaring bitmap columns

OceanBase Database allows you to use the CREATE TABLE or ALTER TABLE statement to create Roaring bitmap columns.

Considerations

If you want to display the query results in a visualized manner, you need to specify the --binary-as-hex parameter when you connect to the client. This parameter specifies to convert binary results into the hexadecimal format.

Examples

The syntax of the CREATE TABLE statement for creating a table with Roaring bitmap columns is as follows:

CREATE TABLE t1 (rb ROARINGBITMAP);

The syntaxes of the ALTER TABLE statement for adding a Roaring bitmap column to and dropping a Roaring bitmap column from a table are respectively as follows:

ALTER TABLE t1 ADD COLUMN rb2 ROARINGBITMAP;
ALTER TABLE t1 DROP COLUMN rb2;

The syntax for setting a default value for a Roaring bitmap column is as follows:

ALTER TABLE t1 MODIFY COLUMN rb ROARINGBITMAP DEFAULT x'0100';

Here are some examples of writing and querying Roaring bitmap data.

// Create a test table.
obclient> CREATE TABLE t2 (
    rb ROARINGBITMAP
);
          DESC t2;

// Insert test data into the table.
obclient> INSERT INTO t2 VALUES (rb_from_string('1,2,3'));
          INSERT INTO t2 VALUES (x'0100');

// Query the data of the test table.
obclient> SELECT * FROM t2;

The return result is as follows:

+----------------------------------+
| rb                               |
+----------------------------------+
| 0x010303010000000200000003000000 |
| 0x0100                           |
+----------------------------------+
2 rows in set