solidDB® can store binary and character data up to 2147483647 (2G - 1) bytes long. When such data exceeds a certain length, the data is called a BLOB (Binary Large OBject) or CLOB (Character Large OBject), depending upon the data type that stores the information. CLOBS contain only “plain text” and can be stored in any of the following data types:
▪CHAR, WCHAR
▪VARCHAR, WVARCHAR
▪LONG VARCHAR (mapped to standard type CLOB)
▪LONG WVARCHAR (mapped to standard type NCLOB)
BLOBs can store any type of data that can be represented as a sequence of bytes, such as a digitized picture, video, audio, a formatted text document. They can also store plain text, but you have more flexibility if you store plain text in CLOBs.
BLOBs are stored in any of the following data types:
▪BINARY
▪VARBINARY
▪LONG VARBINARY (mapped to standard type BLOB)
Since character data is a sequence of bytes, character data can be stored in BINARY fields, as well as in CHAR fields. CLOBs can be considered a subset of BLOBs.
Tip: The term BLOB is used to refer to both CLOBs and BLOBs.
For most non-BLOB data types, such as integer, float, or date, there is a rich set of valid operations that you can do on that data type. For example, you can add, subtract, multiply, divide, and do other operations with FLOAT values. Because a
BLOB is a sequence of bytes and the database server does not know the “meaning” of that sequence of bytes (it does not know whether the bytes represent a movie, a song, or the design of the space shuttle), the operations that you can do on BLOBs are very limited.
solidDB® allows you to perform some string operations on CLOBs. For example, you can search for a particular substring (for example a person’s name) inside a CLOB by using the LOCATE() function. Because such operations require a lot of the server’s resources (memory and/or CPU time), solidDB® allows you to limit the number of bytes of the CLOB that are processed. For example, you might specify that only the first 1 megabyte of each CLOB is searched when doing a string search. For more information, see the description of the MaxBlobExpressionSize configuration parameter in solidDB® Administrator Guide.
Although it is theoretically possible to store the entire BLOB “inside” a typical table, if the BLOB is large, then the server usually performs better if most or all of the BLOB is not stored in the table. In solidDB®, if a BLOB is no more than N bytes long, then the BLOB is stored in the table. If the BLOB is longer than N bytes, then the first N bytes are stored in the table, and the rest of the blob is stored outside the table as disk blocks in the physical database file. The exact value of “N” depends in part upon the structure of the table, the disk page size that you specified when you created the database, and so on, but it is always at least 256. Data that is 256 bytes or shorter is always stored in the table.
If a data row size is larger than one third of the disk block size of the database file, you must store it partly as a BLOB.
The SYS_BLOBS system table is used as a directory for all BLOB data in the physical database file. One SYS_BLOB entry can accommodate 50 BLOB parts. If the BLOB size exceeds 50 parts, several SYS_BLOB entries per BLOB are needed.
The query below returns an estimate on the total size of BLOBs in the database.
select sum(totalsize) from sys_blobs
The estimate is not accurate, because the info is only maintained at checkpoints. After two empty checkpoints, this query should return an accurate response.