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.
Part of the Teradata Physical Design and Data Modelling guide.
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.
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 bookBelow 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.
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
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
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.