What are CLOBs (Character Large Objects)?

Normally, databases save data as follows: In­di­vid­ual table columns are compiled as rows, which are then stacked as data blocks. In each data block, a certain piece of in­for­ma­tion is then assigned to a row. Saving data blocks in this way requires less storage space in the database.

But par­tic­u­lar­ly large data objects are treated dif­fer­ent­ly by databases. These Large Objects (LOBs) are much larger than con­ven­tion­al database entries, as well as un­struc­tured, and are therefore usually stored in a separate storage location. The database then simply creates a reference to the storage location of the object at the cor­re­spond­ing position.

Two types of LOBs exist: BLOBs and CLOBs. A BLOB is a data type that saves large binary objects, while the CLOB file format stores long character strings. The ab­bre­vi­a­tion stands for Character Large Objects and the term was coined by the de­vel­op­ers of the Oracle database. In other database systems, there are also other des­ig­na­tions for large objects, such as TEXT in MySQL and Post­greSQL.

Character Large Objects encompass all database objects with strings – i.e. all objects that contain files con­sist­ing of char­ac­ters. Character strings are sequences of letters, digits, special char­ac­ters, and control char­ac­ters; CLOB entries therefore typically reference text documents (par­tic­u­lar­ly XML). What’s special about storing these large data types is that they are not contained directly in the database – with few ex­cep­tions – but in a separate place. The database itself only contains a reference mechanism that points to the re­spec­tive CLOB. For instance, the entire character code of a website is not stored in a database cell, but instead a reference number that links to the actual storage location of the un­der­ly­ing HTML document.

One of the biggest ad­van­tages of the CLOB data type is the ability to read and edit in­di­vid­ual files. For example, database ad­min­is­tra­tors can use ap­pli­ca­tion programs to compare, edit or merge the content of input Character Large Objects. A brief overview of the editing options is shown below:

• SUBSTR allows users to extract strings from a CLOB
• INSTR inserts cut strings into another position or other CLOBs
• COMPARE compares the values of two CLOBs
• APPEND connects two CLOBs with each other

Since CLOBs store the documents with text or char­ac­ters in full, it’s possible to examine their content in detail. Ad­min­is­tra­tors can determine exactly whether content is du­pli­cat­ed or text parts overlap. If this is the case, the datasets can be combined to free up un­nec­es­sar­i­ly occupied storage space.

In addition, storing letters, digits, special char­ac­ters, and control char­ac­ters as CLOBs is ab­solute­ly loss-free: When saving files, it often depends on whether all in­for­ma­tion within the document actually has to be saved – that isn’t an issue with this data format. For instance, you can also save the meta in­for­ma­tion of a document together with the actual content as a CLOB. Most databases therefore even support the pre­sen­ta­tion of Character Large Objects in a tree structure as well as nav­i­ga­tion and searching for specific content.

The dis­ad­van­tages of this data type for storing enor­mous­ly large numbers of char­ac­ters include the fact that some databases do not allow editing with familiar SQL functions. CLOBs store extremely large quan­ti­ties of text, which means the execution of standard functions would take a very long time. However, at least the ap­pli­ca­tion commands mentioned above – SUBSTR, INSTR, COMPARE, and APPEND – are possible al­ter­na­tives for repli­cat­ing un­avail­able SQL op­er­a­tions. Another dis­ad­van­tage of CLOBs is that rel­a­tive­ly small data elements waste valuable storage space, since a defined, segmented storage space is reserved outside the database.