Breck's statement that the INLINE and PREFIX values are not related to column compression is incorrect. Values shorter than the INLINE value of the column (default 256) are not compressed, and the statement that "The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary" is correct.
The bit in the docs that says "Do not use column compression on columns containing values under 130 bytes" is more of a guideline, since attempting to compress values that small will likely result in a larger value being stored because of the compression overhead. It only applies if you've set your INLINE value to something less than 130 anyway.
answered
09 Sep '10, 14:43
Graeme Perrow
9.6k●3●79●124
accept rate:
54%
Which version do you use? AFAIK, there have been relevant changes w.r.t. column compression (in V10, I think). The CREATE TABLE statement has the INLINE and PREFIX clauses for this, cf. http://dcx.sybase.com/index.html#1200en/dbreference/create-table-statement.htm
Oh, I wasn't aware of the changes, the version I am referring to is 11.0.1 Will the default for prefix mean that everything > 8 Bytes will be compressed? Or is there still an additional margin, because the compression overhead is said to be at least 12 Bytes...
@Volker: The INLINE and PREFIX values affect how a blob is stored (inline with the rest of the row or separately in a blob page). AFAIK they have nothing to do with COMPRESSED.
From the documentation it seems, that at least prefix is also affecting compression: The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary