What are CLOBs (Character Large Objects)?

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

But par­tic­u­larly large data objects are treated dif­fer­ently 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­res­pond­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­ation stands for Character Large Objects and the term was coined by the de­velopers 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­gr­eSQL.

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­larly 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­spect­ive 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­vant­ages of the CLOB data type is the ability to read and edit in­di­vidu­al files. For example, database ad­min­is­trat­ors can use ap­plic­a­tion pro­grammes 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­trat­ors can determine exactly whether content is du­plic­ated or text parts overlap. If this is the case, the datasets can be combined to free up un­ne­ces­sar­ily occupied storage space.

In addition, storing letters, digits, special char­ac­ters, and control char­ac­ters as CLOBs is ab­so­lutely loss-free: When saving files, it often depends on whether all in­form­a­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­form­a­tion of a document together with the actual content as a CLOB. Most databases therefore even support the present­a­tion of Character Large Objects in a tree structure as well as nav­ig­a­tion and searching for specific content.

The dis­ad­vant­ages of this data type for storing enorm­ously large numbers of char­ac­ters include the fact that some databases do not allow editing with familiar SQL functions. CLOBs store extremely large quant­it­ies of text, which means the execution of standard functions would take a very long time. However, at least the ap­plic­a­tion commands mentioned above – SUBSTR, INSTR, COMPARE, and APPEND – are possible al­tern­at­ives for rep­lic­at­ing un­avail­able SQL op­er­a­tions. Another dis­ad­vant­age of CLOBs is that re­l­at­ively small data elements waste valuable storage space, since a defined, segmented storage space is reserved outside the database.