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