LOB types

Large object (LOB) types are used to store TEXT, binary large object (BLOB), JavaScript Object Notation (JSON), and GEOMETRY data.

LOB data can be stored in INROW or OUTROW storage mode.

INROW storage

When INROW storage is used, LOB data is stored together with rows in the primary table. Only one access to storage is required to read LOB data.

OUTROW storage

When OUTROW storage is used, LOB data is stored in an LOB auxiliary table. To read LOB data, the database first obtains locators of the data by reading rows in the primary table and then reads actual LOB data from the LOB auxiliary table based on the locators. The whole process involves two accesses to the storage.

LOB storage switching

Whether INROW or OUTROW storage is used depends on the amount of data in a LOB column. Assume that you have set the storage switching threshold to 8,192 bytes. If data in a LOB column exceeds 8,192 bytes, OUTROW storage is adopted; otherwise, INROW storage is used.

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

The preceding DDL statement sets the INROW-to-OUTROW switching threshold to 8,192 bytes by specifying the LOB_INROW_THRESHOLD parameter.

• When data in a LOB column is within 8,192 bytes, the data is stored together with rows in the primary table.
• When data in a LOB column exceeds 8,192 bytes, the data is stored in a LOB auxiliary table.

INROW storage outperforms OUTROW storage since the former reduces the number of accesses to storage required to read LOB data, thus improving the efficiency. In scenarios where LOB data needs to be accessed frequently, INROW storage can speed up queries and reduce system overhead.

LOB types

The common LOB types in Oracle mode are described as follows:

• BLOB: stores large files such as images, files, and audios.
• CLOB: stores single-byte and multi-byte character data.
• JSON: stores data in the JSON format. It is commonly used to process structured data.
• SDO_GEOMETRY: stores and processes geometric data. It is a composite data type used to represent a two-dimensional or three-dimensional geometric figure.
• XMLType: stores data in the XML format. It can facilitate the processing and query of XML files.

References

• BLOB data type
• CLOB data type
• JSON data types
• SDO_GEOMETRY
• XMLType