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:
|
LOB and XML Subtable Sizing Equation
The following parameter definitions are used with this equation.
| Parameter | Definition |
|---|---|
| BaseRowCount | Estimated cardinality for the base table. |
| OIDSize |
|
| RowOverhead | The number of bytes devoted to subtable row overhead.
|
| 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 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.