Many applications today use data that cannot be stored easily in standard data types, such as INT or CHAR. Instead, a long character or binary format is more suitable. In these cases, the data can be stored as Character Large Objects (CLOBs) or Binary Large Objects (BLOBs). A CLOB includes interpretable characters whose number can be up to 2 billion. A BLOB data type can hold virtually any data that can be stored as a series of binary numbers (8-bit bytes). Typically, BLOBs are used to store large, variable-length data that cannot be easily interpreted as numbers or characters. For example, BLOBs might hold digitized sound, multimedia files, or time-series data read from sensors.
In solidDB, BLOBs are widely supported and there are several different data types to choose from: BINARY, VARBINARY and LONG VARBINARY, of which the last is mapped to the standard data type BLOB.
CLOBs are implemented with one of the following data types, CHAR, WCHAR, VARCHAR, WVARCHAR, LONG VARCHAR, or LONG WVARCHAR. The last two data types are mapped to the standard data types CLOB and NCLOB. For detailed information about BLOB and CLOB data types see BLOBs and CLOBs.
Differences in BLOB and long row handling in disk-based engine and in-memory engine
In solidDB disk-based tables (D-tables), long columns are stored in separate database blocks, the column stores only references to the system table SYS_BLOBS, which stores the block locations for the BLOB. The blocks form a logical chain, but the blocks can be spread over multiple locations avoiding fragmentation issues.
A long column can be designated as a BLOB if the total row size is greater than the maximum row size (one third of the database block size). The column can also be explicitly set as a LOB type.
The D-table BLOB operation is transparent to the user, and applies to all long database object types (BLOB, CLOB, very long columns).
In solidDB in-memory tables (M-tables), a long row claims the next block when the database block size limit is reached. The long row must be in a contiguous area of the memory, which might lead to allocation issues due to fragmentation if very large long rows are constantly used.
Deciding when to use D-table BLOBs or M-table long rows
The D-table LOB store is designed to hold large objects without any fragmentation issues while the M-table long row functionality is designed to remove the row size restriction that is derived from the block size. In general, solidDB M-tables are designed to be efficient with short row sizes, and using very large rows can have a negative effect on performance. However, you might experience performance gains by using M-table long rows instead of D-table rows in read operations.
In summary:
▪ M-tables generally give performance gains (over D-tables) in read-intensive operations and are suited to BLOBs that have a maximum size of around 2MB. You configure the maximum row size by using the MME.LongRowMaxLimit parameter, see LongRowMaxLimit.
▪ D-tables are suited for both intensive read and write operations and can manage much larger BLOBs (maximum size is 2GB).