Best practices in dealing with tables that have wide rows in Teradata

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.
Enthusiast

Best practices in dealing with tables that have wide rows in Teradata

Hi,

 

I would like to see if there are any strategies or best practices in dealing with tables that have wide rows. The issues is that we have one very wide table and the request is to add additional columns to the table; however, by adding the columns to the table will have it exceed the 64000 bytes.

 

These are the current Byte Size counts of the columns, which I don’t think take into account any row overheads

       Existing Table: 76 columns  45050 bytes

       Existing Table Varchar 2000: 9 columns  36000 bytes

      

       New Columns: 40 columns  49802 bytes

       New Columns Varchar 2000: 12 columns 48000 bytes

 

My initial inclination is to break out the text fields into a normalized table with a structure similar to below

 

PROJ_ID

TABLE_NAME – Which in this case would be PROJ and would allow other tables to use this table as need provided the text is at a project level.

Text Type – Text which would contain the text contents, ie column name.

Text VARCHAR(2000)

 

From my understanding if there were 2 wide tables and a view was used to join the two table together, you would still be limited to the 64,000 byte limit.

 

Some of the options that have been thrown out there.

  1. Put new columns in a second table and use a view to join the two tables.
  2. Put the large text fields in separate table.
  3. Put the large text fields into CLOBS.

 

The application that will be consuming the data is Tableau, and the developer would prefer a view to retrieve a wide row.

Thanks for any insight and advice.

 

1 REPLY
Highlighted
Teradata Employee

Re: Best practices in dealing with tables that have wide rows in Teradata

Prior to TD16/TTU16, result rows are limited to 64KB - so even if you split the columns across multiple tables for storage, you wouldn't be able to return all the columns in a single wide row.

 

And I don't think Tableau would support CLOB.