Estimating Pages That Simple Large Objects Occupy

The following methods for estimating blobpages yield a conservative (high) estimate because a single TEXT or BYTE column does not necessarily occupy the entire blobpage within a tblspace. In other words, a blobpage in a tblspace can contain multiple TEXT or BYTE columns.

To estimate the number of blobpages

Obtain the page size with onstat -b.
Calculate the usable portion of the blobpage with the following formula:
```
bpuse = pagesize - 32
```
For each byte of blobsize n, calculate the number of pages that the byte occupies (bpages_n) with the following formula:
```
bpages1 = ceiling(bytesize1/bpuse)
bpages2 = ceiling(bytesize2/bpuse)
...
bpages_n = ceiling(bytesize_n/bpuse)
```
The ceiling() function indicates that you should round up to the nearest integer value.
Add up the total number of pages for all simple large objects, as follows:
```
blobpages = bpages1 + bpages2 + ... + bpagesn 
```

Alternatively, you can base your estimate on the median size of simple large objects (TEXT or BYTE data); that is, the simple-large-object data size that occurs most frequently. This method is less precise, but it is easier to calculate.

To estimate the number of blobpages based on the median size of simple large objects

Calculate the number of pages required for simple large objects of median size, as follows:
```
mpages = ceiling(mblobsize/bpuse)
```
Multiply this amount by the total number of simple large objects, as follows:
```
blobpages = blobcount * mpages 
```

Storing Simple Large Objects in the Tblspace or a Separate Blobspace

When you create a simple-large-object column on magnetic disk, you have the option of storing the column data in the tblspace or in a separate blobspace. You can often improve performance by storing simple-large-object data in a separate blobspace, as described in Estimating Pages That Simple Large Objects Occupy, and by storing smart large objects and user-defined data in sbspaces.

(You can also store simple large objects on optical media, but this discussion does not apply to simple large objects stored in this way.)

In the following example, a TEXT value is stored in the tblspace, and a BYTE value is stored in a blobspace named rasters:

A TEXT or BYTE value is always stored apart from the rows of the table; only a 56-byte descriptor is stored with the row. However, a simple large object occupies at least one disk page. The simple large object to which the descriptor points can reside in the same set of extents on disk as the table rows (in the same tblspace) or in a separate blobspace.

When simple large objects are stored in the tblspace, the pages of their data are interspersed among the pages that contain rows, which can greatly increase the size of the table. When the database server reads only the rows and not the simple large objects, the disk arm must move farther than when the blobpages are stored apart. The database server scans only the row pages in the following situations:

Another consideration is that disk I/O to and from a dbspace is buffered in shared memory of the database server. Pages are stored in case they are needed again soon, and when pages are written, the requesting program can continue before the actual disk write takes place. However, because blobspace data is expected to be voluminous, disk I/O to and from blobspaces is not buffered, and the requesting program is not allowed to proceed until all output has been written to the blobspace.

For best performance, store a simple-large-object column in a blobspace in either of the following circumstances:

Estimating Tblspace Pages for Simple Large Objects

In your estimate of the space required for a table, include blobpages for any simple large objects that are to be stored in that tblspace.

For a table that is both relatively small and nonvolatile, you can achieve the effect of a dedicated blobspace by separating row pages and blobpages, as the following steps explain.

To separate row pages from blobpages within a dbspace

Load the entire table with rows in which the simple-large-object columns are null.
Create all indexes.
The row pages and the index pages are now contiguous.
Update all the rows to install the simple large objects.
The blobpages now appear after the pages of row and index data within the tblspace.