Skip to main content

LOB types

The LOB type is used to store data of types such as TEXT, BLOB, JSON, and Geometry. The LOB type supports inline storage (INROW) and out-of-line storage (OUTROW).

Inline storage​

Inline storage stores LOB data together with the main table row. When you read LOB data, you need to perform only one storage access operation.

Out-of-line storage​

Out-of-line storage stores LOB data in a LOB auxiliary table. When you read LOB data, you need to read the main table row to obtain the locator of the out-of-line LOB, and then read the actual LOB data from the LOB auxiliary table based on the locator information. In this process, you need to perform two storage access operations.

LOB storage conversion​

Whether LOB data is stored inline or out-of-line depends on the amount of data in the LOB column. For example, if you set a threshold of 8192 bytes, LOB data that exceeds 8192 bytes is stored out-of-line, and LOB data that does not exceed 8192 bytes is stored inline.

CREATE TABLE t(pk int, data text) LOB_INROW_THRESHOLD = 8192;

The preceding DDL statement specifies that the threshold for converting LOB columns from inline to out-of-line storage is 8192 bytes. The LOB_INROW_THRESHOLD parameter indicates the threshold for LOB columns.

  • When the data in a LOB column is less than or equal to 8192 bytes, the LOB data is stored inline with the main table row.
  • When the data in a LOB column exceeds 8192 bytes, all the data is stored in the LOB auxiliary table.
info

To change the lob_inrow_threshold value from a larger value to a smaller value, you need to trigger an offline DDL operation.

Inline storage performs better than out-of-line storage. It reduces the number of storage access operations and improves the efficiency of reading LOB data. In scenarios where LOB data is frequently accessed, choosing inline storage can speed up queries and reduce system overhead.

LOB types​

Common LOB types are listed in alphabetical order:

  • ARRAY: Used to store array data types, allowing the storage of multiple values in a collection.
  • Roaringbitmap: Used to store bitmap data, primarily for image processing and representation.
  • BLOB (Binary Large Object): Used to store binary data, such as images or files, with a maximum length of 65,535 bytes.
  • GEOMETRY: Used to store spatial data, supporting spatial analysis and operations.
  • JSON: Used to store JSON-formatted data, facilitating the handling of structured data.
  • LONGTEXT: Used to store large amounts of text data, with a maximum length of 536,870,910 bytes.
  • LONGBLOB: Used to store large amounts of binary data, with a maximum length of 536,870,910 bytes.
  • MEDIUMBLOB: Used to store moderate amounts of binary data, with a maximum length of 16,777,215 bytes.
  • MEDIUMTEXT: Used to store moderate amounts of text data, with a maximum length of 16,777,215 bytes.
  • TEXT: Used to store small amounts of text data, with a maximum length of 65,535 bytes.

References​

Contents

Edit this page