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 usage | Average size of recommended data type | Explanation | ||
---|---|---|---|---|
32,000 bytes or less | More than 32,000 bytes | |||
Frequency of binary data specification in projection columns | High | BINARY | BLOB |
|
Low | BLOB# | BLOB | BLOB 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 flexibility | BINARY | Equal | For 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 efficiency | BINARY | BINARY 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/updates | BLOB | The larger the concatenation data size, the better the performance of BLOB type. | ||
Frequent partial extractions | Equal | BLOB | If 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 emphasized | BINARY | BLOB 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 change | BINARY# | BLOB | If data of more than 32,000 bytes is handled, BLOB type is recommended. If the data size is relatively small, BINARY type is recommended. |