Ok, this is not a mystery at all.
Compression in SAP HANA mostly works by replacing duplicate data entries with pointers or aggregated entries (e.g. the repeating value 10 10 10 10 could be stored as 4 x 10 and so on).
Now the column store stores information in a split up way.
1. it stores all different values once (that's what we call the dictionary)
2. it saves the occurrence of a value in a record by storing a pointer (up to 4 byte integer) to the dictionary entry.
This approach works pretty well for values that take more space than the pointer and that are repeated often. Think of the three character (6 Unicode bytes) long, ever repeating client (MANDT) column.
Here we store the value once and only point to it with actually just 2 bits - since the dictionary only contains 1 value.
Now look at your example:
The number of different values (distinct values) is nearly as many as you've got rows in your table.
That means, the SID here is close to be unique; which gives us little space to compress 'duplicates'.
Then, the data type of the SID is usually an integer, which means, even if we would have lots of duplicates, the saving by using a reference instead of the value might be rather small.
Finally, this column has an inverted index set up with it. This means, that the pointers to the value occurrences are actually stored twice.
One time linking the occurrence to the value and one time linking the value to the occurrences (hence inverted index).
And there we have your threefold space requirement.
There's plenty of information available meanwhile how column store compression works (not the least one to mention Richard's and my book ) so I think this should answer the question well enough for now.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.