What is Row Store and Column Store Approach of SAP HANA
Row based tables:
It is the traditional Relational Database approach
It store a table in a sequence of rows
Column based tables:
It store a table in a sequence of columns i.e. the entries of a column is stored in contiguous memory locations.
SAP HANA is highly optimized for column-order storage.
SAP HANA uses and support both row-based and column-based approach making it very powerful and efficient for processing huge data quickly
Row based tables benefits:
The application needs to only process a single record at one time (many selects and/or updates of single records).
The application needs to access a complete record (or row).
Neither aggregations nor fast searching are required.
The table has a small number of rows (e. g. configuration tables, system tables).
Row based tables dis-advantages:
In case of analytic applications where aggregation are used and fast search and processing is required. In row based tables all data in a row has to be read even though the requirement may be to access data from a few columns.
Row store compression is very limited.
Advantages of column-based tables?
Faster Data Access:
Only affected columns have to be read during the selection process of a query. Any of the columns can serve as an index.
Columnar data storage allows highly efficient compression because the majority of the columns contain only few distinct values (compared to number of rows).
parallel Processing capacbility
In a column store, data is already vertically partitioned. This means that operations on different columns can easily be processed in parallel. If multiple columns need to be searched or aggregated, each of these operations can be assigned to a different processor core