Data encoding and compression

OceanBase Database supports both MySQL and Oracle modes. To accommodate users with different usage habits, OceanBase Database provides different configuration options for data encoding and compression in each mode.

OceanBase Database supports the use of DDL statements to configure the encoding and compression of data tables.

Configure data compression and encoding in MySQL mode

When you create or modify a table in MySQL mode, you can set the row_format and compression parameters to specify the encoding format and compression algorithm.

The SQL syntax is as follows:

• Specify the encoding format and compression algorithm when you create the table

   CREATE TABLE table_name table_definition_list
   ROW_FORMAT [=] row_format COMPRESSION [=] 'compression';
  

  For more information about the CREATE TABLE statement, see CREATE TABLE.

• Modify the encoding format and compression algorithm when you modify the table

   ALTER TABLE table_name [alter_table_action_list] [SET] ROW_FORMAT [=]row_format COMPRESSION [=]  'compression';
  

  For more information about the ALTER TABLE statement, see ALTER TABLE.

Valid values of the row_format parameter are as follows:

• REDUNDANT and COMPACT: Data is not encoded and is saved in the flat format.

• DYNAMIC: Data is encoded and saved in the encoding format. The default value is DYNAMIC.

• COMPRESSED: Data is encoded and saved in the encoding format.

• CONDENSED: Data is encoded and saved in the selective encoding format.

  The selective encoding format is a subset of the encoding format and is more query-friendly.

Valid values of the compression parameter are as follows:

• none: Data is not compressed.

• lz4_1.0: Data is compressed by using the lz4_1.0 algorithm.

• snappy_1.0: Data is compressed by using the snappy_1.0 algorithm.

• zstd_1.0: Data is compressed by using the zstd_1.0 algorithm.

• zstd_1.3.8: Data is compressed by using the zstd_1.3.8 algorithm. By default, the zstd_1.3.8 algorithm is used.

• lz4_1.9.1: Data is compressed by using the lz4_1.9.1 algorithm.

Here are some examples:

• Specify the encoding format and compression algorithm when you create the table

  Execute the following statement to set the encoding format to encoding and the compression algorithm to zstd_1.0 when you create a table:

   obclient> CREATE TABLE test (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
   ) ROW_FORMAT=CONDENSED COMPRESSION='zstd_1.0';
  

• Modify the encoding format and compression algorithm when you modify the table

  Execute the following statement to change the encoding format to selective encoding and the compression algorithm to lz4_1.0:

   obclient> ALTER TABLE test SET ROW_FORMAT = CONDENSED COMPRESSION = 'lz4_1.0';
  

Configure data compression and encoding in Oracle mode

When you create or modify a table in Oracle mode, you can specify the data encoding format and compression algorithm.

The SQL syntax is as follows:

• Specify the encoding format and compression algorithm when you create the table

   CREATE TABLE table_name table_definition_list compression;
  

  For more information about the CREATE TABLE statement, see CREATE TABLE.

• Modify the encoding format and compression algorithm when you modify the table

   ALTER TABLE table_name [alter_table_action_list] compression;
  

  For more information about the ALTER TABLE statement, see ALTER TABLE.

Valid values of the compression parameter are as follows:

• NOCOMPRESS: Data is not encoded or compressed, and is saved in the flat format.

• COMPRESS BASIC: Data is not encoded but is saved in the flat format and compressed by using the lz4_1.0 algorithm.

• COMPRESS FOR OLTP: Data is not encoded but is saved in the flat format and compressed by using the zstd_1.3.8 algorithm.

• COMPRESS FOR QUERY: Data is encoded, saved in the encoding format, and compressed by using the lz4_1.0 algorithm.

• COMPRESS FOR ARCHIVE: Data is encoded, saved in the encoding format, and compressed by using the zstd_1.3.8 algorithm. By default, COMPRESS FOR ARCHIVE is used.

• COMPRESS FOR ARCHIVE HIGH: Data is encoded, saved in the encoding format, and compressed by using the zstd_1.3.8 algorithm.

• COMPRESS FOR QUERY LOW: Data is encoded, saved in the selective encoding format, and compressed by using the lz4_1.0 algorithm.

  The selective encoding format is a subset of the encoding format and is more query-friendly.

Here are some examples:

• Specify the encoding format and compression algorithm when you create the table

  Execute the following statement to set the encoding format to encoding and the compression algorithm to lz4_1.0 when you create a table:

   obclient> CREATE TABLE test (
   id number NOT NULL,
   fname VARCHAR2(30),
   lname VARCHAR2(30),
   hired DATE NOT NULL DEFAULT sysdate,
   separated DATE,
   job_code INT NOT NULL,
   store_id INT NOT NULL
   ) COMPRESS FOR QUERY;
  

• Modify the encoding format and compression algorithm when you modify the table

  Execute the following statement to change the table encoding format to encoding and the compression algorithm to zstd_1.3.8:

   obclient> ALTER TABLE test COMPRESS FOR ARCHIVE;
  

Set the storage format for dumped data

In previous versions, OceanBase Database only supported the flat format for storing dumped data. However, in scenarios with a large amount of incremental data, the flat format became a performance bottleneck. To improve query performance in OLAP scenarios and reduce the storage space required for incremental data, OceanBase Database supports the encoding format starting from V4.6.0. You can specify the storage format for dumped data using the table attribute delta_format.

• Specify the storage format for dumped data when you create a table

  CREATE TABLE table_name delta_format = 'flat | encoding' ;
  

  Note

  Alternatively, you can specify the storage format for dumped data for all newly created tables using the tenant-level parameter default_delta_format without specifying it when you create a table.

• Change the storage format for dumped data when you modify a table

  ALTER TABLE table_name SET delta_format = 'flat | encoding';
  

  The delta_format parameter supports the following two values:

  • flat: Specifies that the flat format is used for storing dumped data. The flat format is the default storage format for dumped data, which is suitable for OLTP scenarios. It has a fast write speed and low compression ratio, but is not friendly to queries in OLAP scenarios.

  • encoding: Specifies that the encoding format is used for storing dumped data. The encoding format is suitable for OLAP scenarios. It has a slower write speed and higher compression ratio, and is friendly to queries in OLAP scenarios. It has a more significant effect on delete_insert tables.

Here are some examples:

Change the compression algorithm for SSTables

To change the compression algorithm for SSTables without causing a huge volume of I/O writes in major compactions, you need to launch a progressive compaction to rewrite all the data microblocks. The number of rounds of progressive compactions can be determined by specifying the progressive_merge_num parameter in the ALTER TABLE statement.

To do so, perform the following steps:

1. Log in to the database as a tenant administrator.

2. Execute the following statement to set the number of rounds of progressive compactions.

   The progressive_merge_num parameter specifies the number of rounds of progressive compactions on a table. The default value is 0, which indicates incremental compaction. If you set the parameter to 1, a full compaction is performed.

   To set two rounds of progressive compactions, execute the following statement:

   obclient> ALTER TABLE t1 SET progressive_merge_num=2;
   

   After the parameter is set, data is progressively rewritten during major compactions each day.

3. Change the compression algorithm.

   For more information about how to change the compression algorithm in MySQL mode, see Configure data compression and encoding in MySQL mode in this topic.

   For more information about how to change the compression algorithm in Oracle mode, see Configure data compression and encoding in Oracle mode in this topic.