Sizing Types of Subtables | Teradata Vantage - 17.10 - Sizing a LOB or XML Subtable - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

One LOB or XML subtable is required for each column in a base table defined with a BLOB, CLOB, or XML data type. Depending on the size of each LOB or XML string, the cardinality of the subtable can exceed the cardinality of the base table. Estimate the size of your LOB and XML subtables using the equations provided in LOB and XML Subtable Sizing Equation below.

Because LOBs and XML strings are variable length entities, there are no alignment issues for aligned row format systems.

LOB and XML Subtable Rows and Skew

Each LOB or XML string is stored in 64 KB sections in a subtable, except for the row that stores the last section (because the number of bytes in a LOB or XML string is typically not an exact multiple of 64 KB). This means that for any LOB or XML string greater than 64 KB, there is not a 1:1 equivalence between the number of base table rows and the number of LOB or XML subtable rows supporting those base table rows. As a result, unless every LOB or XML string in a table has the identical size, it is likely that storage skew will occur.

IF the primary index for a base table is a … THEN …
UPI this skew does not affect performance in any way, but it is likely to produce an asymmetric storage effect on storage requirements.
NUPI both of the following assertions are true:
  • Performance costs accrued by a skewed distribution of base table rows are likely to be further magnified.
  • Storage skew is even more likely to occur than for tables with a UPI.

LOB and XML Subtable Sizing Equation

The following parameter definitions are used with this equation.

     Parameter                                                           Definition
BaseRowCount Estimated cardinality for the base table.
  • 40 bytes for nonpartitioned primary index and 2-byte PPI tables.
  • 45 bytes for 8-byte PPI tables.
RowOverhead The number of bytes devoted to subtable row overhead.
  • If the base table has a non-partitioned primary index, the subtable row overhead is 12 bytes.
  • If the base table has a partitioned primary index, the subtable row overhead is 16 bytes.
AvgLOBSize Average LOB or XML string size for the table.

Remember that all row lengths must be an even number of bytes (see Byte Alignment), so be sure to take this into account.

LOB subtable size equation

LOB and XML Subtables and Fallback

The LOB or XML subtable size must be doubled if the base table for which the LOB or XML column is defined is fallback protected.