Row store is is best for :
- Table mainly contains distinct values
- Low compression rate
- All columns of are relevant
- No aggregation or search on non-indexed columns
- Table is fully buffered
- Table has a minor number of records
- Table contains unstructured data
Column store is best for :
- Column based operations on large number of rows
- Large number of columns, many unused
- Aggregations and intensive search
- Master and transactional data
Columnar Dictionary Compression
First the Column “Country” is sorted alphabetically like Belgium,Denmark,France, Italy, Spain as shown below. Since the Logical Table has 8 entries in total ie from Order 456 to Order 463 as shown below.
So as per below Column compression diagram we will have the entries like France ( index 3 of Dictionary diagram ) is on 1st index of the Compressed column diagram , similarly Denmark ( index 2 of Dictionary diagram) is on 5th index of the Compressed column diagram etc.
Inverted Index
Here after the Compressed Column, for the Inverted Index we have 5 entries as shown in the Diagram just like Dictionary Diagram . Here as per Dictionary Diagram Belgium comes at 7th position of the logical table, similarly Denmark comes twice in the logical table at 5,6 positions and so on.
