Storage size impact

The following factors account for the difference between in-row and out-row storage usage:

• Length of the data.
• Database BLOCKSIZE. More specifically, the block size of the tablespace on which the LOB resides.
• CHUNKSIZE, which is a parameter that you can specify when creating the large object (LOB). The minimum for this parameter is the block size of the tablespace on which the LOB resides. The chunk size can also be a multiple of the block size.

Assuming that the default Oracle 8 kilobyte (KB) block size is used, the in-row option uses less storage space than the out-row option when the following criteria are met (because the LOB value is stored in the table):

• The size of the LOB value is less than 4000 bytes.
• The in-row option is specified.

A simple test that populated a BMC Remedy AR System diary field with 3500 characters showed that the in-row option uses significantly less storage than the out-row option. This test consisted of 10,500 rows. The other required field values remained constant.

The following SQL statement was issued to find the storage size of the table and its LOB segment. A LOB segment is created regardless of the LOB storage properties.

SELECT bytes, segment_type
FROM user_segments
WHERE segment_name IN('T1344','SYS_LOB0001143348C00009$$');

The total bytes from the table and LOB segment were added to give the total storage space. The following table shows that the in-row option saves 50,266,112 bytes of storage when LOB value sizes are less than 4000 bytes.

Storage bytes of values that have fewer than 4000 characters

Out-row storage has the following characteristics, which result in the large use of storage space:

• If the table is created with the out-row property and the LOB holds any data, a minimum of one chunk of out-of-line storage space is used, even when the size of the LOB is less than the CHUNKSIZE.
• When the size of the LOB is greater than 4000 bytes, regardless of the LOB storage properties for the column, it is stored as out-row in another segment outside the table. However, the LOB locators are always stored in the row.

Another test with 10,000 characters in the diary field showed that for both in-row and out-row options, the storage sizes were equivalent. This test consisted of 10,500 rows. The other required field values remained constant.

Storage bytes of values that have more than 4000 characters

This result shows that if the in-row option is used as the default, storage space is reduced when the LOB value is less than 4000 bytes. Storage space is not impacted when the LOB value is greater than 4000 bytes because this is equivalent to using the out-row option. This situation is also true for BMC Remedy AR System attachment fields (BLOBs). By default, attachment fields use the in‑row option and cannot be changed.

Finally, a similar test was conducted by populating 50 characters in the diary field for the in-row and out-row storage options for 10,500 rows.

Storage bytes of 50 characters

In this case, the out-row space usage is significantly higher than the in-row space usage. The out-row option uses more space for small data sizes.