Each text or image or Java off-row column stores a 16-byte pointer in the data row with the datatype varbinary(16). Each column that is initialized requires at least 2K (one data page) of storage space.
Columns store implicit null values, meaning that the text pointer in the data row remains null and no text page is initialized for the value, saving 2K of storage space.
If a LOB column is defined to allow null values, and the row is created with an insert statement that includes NULL for the column, the column is not initialized, and the storage is not allocated.
If a LOB column is changed in any way with update, then the text page is allocated. Of course, inserts or updates that place actual data in a column initialize the page. If the column is subsequently set to NULL, a single page remains allocated.
Each LOB page stores approximately 1800 bytes of data. To estimate the number of pages that a particular entry will use, use this formula:
Data length / 1800 = Number of 2K pages |
The result should be rounded up in all cases; that is, a data length of 1801 bytes requires two 2K pages.
The total space required for the data may be slightly larger than the calculated value, because some LOB pages store pointer information for other page chains in the column. Adaptive Server uses this pointer information to perform random access and prefetch data when accessing LOB columns. The additional space required to store pointer information depends on the total size and type of the data stored in the column. Use the information in Table 11-3 to estimate the additional pages required to store pointer information for data in LOB columns.
Data Size and Type |
Additional Pages Required for Pointer Information |
|---|---|
400K image |
0 to 1 page |
700K image |
0 to 2 pages |
5MB image |
1 to 11 pages |
400K of multibyte text |
1 to 2 pages |
700K of multibyte text |
1 to 3 pages |
5MB of multibyte text |
2 to 22 pages |
