Long texts like descriptions are often stored in columns of data type VARCHAR(); if the column is defined as a character set LATIN, we can store up to 64000 characters. If specified as character set UNICODE, we can store up to 32000 characters. The more characters are stored in a VARCHAR column, the fewer rows fit into a data block, and full table scans become more expensive.

The Teradata CLOB Datatype

Character large objects (CLOB) can be an excellent alternative for storing long strings. The data of a CLOB column is stored as a separate sub-table. Separating CLOB’s from the base table can give a significant performance improvement for queries that are accessing the table with a full table scan as fewer data blocks need to be read if the CLOB column is not selected. Fewer data blocks mean fewer IOs.

Here is an example table, we are assuming that the column “Desc” of each table row contains lengthy textual information:

CREATE TABLE Varchar_Table
COl1 CHAR(01),
Desc VARCHAR(30000)

In the absence of a secondary index, all data blocks have to be copied from disk to FSG cache, even if we are only interested in the column “ID” content. As we are assuming that all rows contain lengthy textual information in column Desc this will cause a lot of IOs:

SELECT ID FROM Varchar_Table WHERE COl1 = ‘X’  ;

By changing the data type from VARCHAR() to CLOB, resource consumption can be decreased significantly; many more rows can be packed into each data block, less disk transfer is required:

COl1 CHAR(01),

SELECT ID FROM Varchar_Table WHERE COl1 = ‘X’;  — fewer blocks have to be moved from disk to FSG cache

No performance advantage is given if the CLOB column is part of the select list. In this case, the base table plus the sub-table holding the CLOB data have to be read:

SELECT ID,Desc FROM Varchar_Table WHERE COl1 = ‘X’  ;

Buy now at Amazon
  • Michelle West says:

    I feel like I’m missing something. The text explaining they benefit of the CLOB is very clear and understandable. But none of your examples reference the CLOB table. And you talk about joining to the CLOB table but no example is provided, and was what I was looking for!!!

    • The CBLOB sub-table is used automatically by Teradata. It is maintained by the system and not visible to us. Whenever your SELECT column list contains the CLOB column, it is taken from the sub-table, otherwise, only the base table has to be accessed.

      I hope this made it clearer

  • David Wellman says:

    So, in this fairly specific scenario, you get ‘columnar’ by the back door. I like it! I useful little trick, will have to see how it works in practice but this is definitely worth considering. One possible issue that comes to mind is the handling of CLOBs. They are not straight forward, but if the tool you’re using to query the data is ok with them then this should be fine.
    Even without that issue, this is a nice idea.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like