The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

The documentation recommends not to compress columns with less than 130 chars. Anyway the sa_column_stats procedure has a value num_values_compressed. So there seems to be a mechanism which prevents to compress rows which have a value of less than 130 chars and still compress rows with values where compression would be beneficial.

Is this correct?

asked 09 Sep '10, 09:58

Martin's gravatar image

accept rate: 14%

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.

(09 Sep '10, 10:27) Volker Barth

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...

(09 Sep '10, 10:47) Martin

@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.

(09 Sep '10, 11:28) Breck Carter

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

(09 Sep '10, 12:54) Martin

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.

permanent link

answered 09 Sep '10, 14:43

Graeme%20Perrow's gravatar image

Graeme Perrow
accept rate: 51%


Great to know, so normally leaving everything default will mean that only the data in the extended pages will be compressed for varchar columns. Maybe this should be stated more explicit in the documentation.

(09 Sep '10, 17:10) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 09 Sep '10, 09:58

question was seen: 879 times

last updated: 09 Sep '10, 14:43