Create a highly compressed bitmap column

OceanBase Database allows you to create a highly compressed bitmap column by using the CREATE TABLE or ALTER TABLE statement.

Considerations

• To view the query results in a visualized manner, you need to add the --binary-as-hex parameter when you connect to the client to convert the binary results to hexadecimal.

Examples

You can create a table with a highly compressed bitmap column by using the CREATE TABLE statement. Here is the syntax:

CREATE TABLE t1 (rb ROARINGBITMAP);

You can add or drop a highly compressed bitmap column in an existing table by using the ALTER TABLE statement. Here is the syntax:

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

You can set the default value for a highly compressed bitmap column. Here is the syntax:

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

Here are some examples of writing and querying highly compressed bitmap data:

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

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

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

The return result is as follows:

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