DWHPro Insights

Improve Query Performance by Using the Teradata CLOB Data Type for Storing Longer Texts

By Roland Wenzlofsky ·

Improve Query Performance by Using the Teradata CLOB Data Type for Storing Longer Texts

VARCHAR limits depend on character set and release and are distinct from total row and client-response limits. Long inline values can reduce rows per block and increase scanning work.

The Teradata CLOB Data Type

CLOB can reduce base-row width when long values are not selected. The benefit is workload-dependent: retrieving the LOB adds work, and client support, LOB size and SQL-operation restrictions must be checked. The corrected VARCHAR and CLOB definitions executed, but no I/O improvement was measured.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Below is an example table with detailed descriptions in each row’s “DescriptionText” column.

CREATE TABLE Varchar_Table
(
ID INTEGER NOT NULL,
COl1 CHAR(01),
DescriptionText VARCHAR(30000)
) PRIMARY INDEX (ID);

A base-table scan must examine applicable base blocks; physical disk reads depend on caching and the execution plan. The example is not a measured I/O comparison.

SELECT ID FROM Varchar_Table WHERE COl1 = 'X' ;
CREATE TABLE CLOB_Table
(
ID INTEGER NOT NULL,
COl1 CHAR(01),
DescriptionText CHARACTER LARGE OBJECT
) PRIMARY INDEX (ID);
SELECT ID FROM CLOB_Table WHERE COl1 = 'X'; -- potential base-row benefit; measure on the target workload

Selecting a CLOB column does not confer any performance advantage. Instead, both the base table and the sub-table containing the CLOB data must be read.

SELECT ID,DescriptionText FROM CLOB_Table WHERE COl1 = 'X' ;

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “Improve Query Performance by Using the Teradata CLOB Data Type for Storing Longer Texts”

  1. 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!!!

    Reply
    • 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

      Reply
  2. 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.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam