12.12.3 BLOB type and BINARY type usage

The following table lists recommended data types for each binary data usage.

Table 12-4 Recommended data type for each binary data usage

Binary data usageAverage size of recommended data typeExplanation
32,000 bytes or lessMore than 32,000 bytes
Frequency of binary data specification in projection columnsHighBINARYBLOB
  • For 32,000 bytes or less, BINARY type can also use block transfer. A row's entire data is together, yielding better performance.
  • For more than 32,000 bytes, BLOB type can use less memory processing large object data, yielding better performance.
LowBLOB#BLOBBLOB type requires a smaller data size for base rows than BINARY type, so if there are many pieces of data, BLOB will yield better performance. However, if indexes are defined for the non-BINARY columns, index scans will eliminate the difference between BINARY and BLOB types. Index scans are recommended, but the larger the segment size the smaller the performance difference.
SQL descriptor flexibilityBINARYEqualFor 32,000 bytes or less, if there are no index definitions, BINARY type SQL descriptors can be roughly the same as the VARCHAR descriptor. Therefore, the SQL descriptor range is broader than for the BLOB type. For details, see the manual HiRDB Version 9 SQL Reference.
Data storage efficiencyBINARYBINARY type has better storage efficiency. However, for large data objects, for which the 8-kilobyte boundary can be ignored, there is little difference.
Frequent additions/updatesBLOBThe larger the concatenation data size, the better the performance of BLOB type.
Frequent partial extractionsEqualBLOBIf partial extraction is done on a BINARY type with a large stored data size, the performance will be very poor. Further, the greater the frequency of partial extraction, the poorer the performance. If partial extraction will be required frequently against large data, BLOB type is recommended.
Operability is emphasizedBINARYBLOB type requires special operations, such as backing up the user LOB RDAREA.
If you cannot determine from the above methods or for a possible future policy changeBINARY#BLOBIf data of more than 32,000 bytes is handled, BLOB type is recommended. If the data size is relatively small, BINARY type is recommended.
#: If the data size is near to the page size, and if the BINARY type is used with a large number of table scans, performance will be much poorer than for the BLOB type. To avoid this, change from a table scan to an index scan. Index scans are recommended, and even with a large segment size the performance difference is small.