In order for you to understand how column and row store works in SAP HANA we need to know what Column and Row store is? and for start the best way would be to show you illustration of this in a picture bellow then I will go head and discuss this further in this article, I will be highlighting their benefit and when to use this, these are some of my recommendation and you are free to use them any where you feel like you need them.
As you can see we have two kinds of structured tables one is when our data is designed and stored in column then we store them in column tables and if its row store we use that as our table structure.
Now the question is when do we need to use column or row stores and the simplest answer would be when we need to re-structure the whole data in a table in a database in other word if we work in entire column of a table then we use row store, as re-structuring the data in a database is the most expensive job for hardware, and when we have huge data or huge number of columns to work with to analyses big chunk of data or data need to be aggregated then only we go for Row bases store.
Row Store is recommended in SAP HANA if?
I’m going to list some key points of when to use the Row store data but this does not means they are the only points there would be many other but for sure these are the main points of when to use row store data.
- The table has small number of rows like configuration tables, Meta data and log tables.
- The application need to access data from table one row at a time like (multiple CRUD of single records.
- Application need to access complete record from table.
- Row Store is used in SAP HANA for Metadata, for application server internal data such as ABAP, server system tables etc.
- You have the choice to use either Column or Row bases tables or mix of both.
Column store is recommended SAP HANA if?
And here is some key point of when to use Column store data, might be other points as well but these are the general main point.
- Calculation is limited or done only in some columns not all of them or a few columns only not all columns.
- We will be searching our tables based on few columns only.
- The table has large number of columns
- The table has many rows and large number of columnar aggregation is required.
The main key benefit of column store is Massive Parallel processing MPP, and this not only give us the speed plus it will give us more power on compression we can compress this 10 times more than conventional database.