Row store vs Column store in HANA

A database table is a two-dimensional data structure with cells organized in rows and columns. Computer memory however is organized as a linear structure. To store a table in linear memory, two options.

A row-oriented storage stores a table as a sequence of records, each of which contains the fields of one row. Conversely, in a column store the entries of a column are stored in contiguous memory locations.

The concept of columnar data storage has been used for quite some time. Historically it was mainly used for analytics and data warehousing where aggregate functions play an important role. Using column stores in OLTP applications requires a balanced approach to insertion and indexing of column data to minimize cache misses.

The SAP HANA database allows the developer to specify whether a table is to be stored column-wise or row-wise.

It is also possible to alter an existing table from columnar to row-based and vice versa.


Row based tables have advantages in the following circumstances: 

1. The application needs to only process a single record at one time (many selects and/or updates of      single records).

2.The application typically needs to access a complete record (or row).

3.The columns contain mainly distinct values so that the compression rate would be low.

4.Neither aggregations nor fast searching are required.

5.The table has a small number of rows (e. g. configuration tables).
To enable fast on-the-fly aggregations, ad-hoc reporting, and to benefit from compression mechanisms it is recommended that transaction data is stored in a column-based table.

The SAP HANA data-base allows joining row-based tables with column-based tables. However, it is more efficient to join tables that are located in the same row or column store. For example, master data that is frequently joined with transaction data should also be stored in column-based tables.

Column-based tables have advantages in the following circumstances:

1. Calculations are typically executed on single or a few columns only.
2. The table is searched based on values of a few columns.
3.The table has a large number of columns.
4. The table has a large number of rows and columnar operations are required (aggregate, scan           etc.)
5. High compression rates can be achieved because the majority of the columns contain only few distinct values (compared to number of rows).

COLUMN STORAGE IS BEST SUITABLE FOR MODERN CPU'S:

Modern CPUs with multi-core architecture provide anenormous amount of computing power. Blades with 8 CPUs and 16 cores per CPU will populate next-generation blade servers. That gives us 128 computing units with up to approximately 500 GB of main memory. To optimize the use of
these computing devices we have to understand memory hierarchies,cache sizes, and how to enable parallel processing within one program [6].

We consider the memory situation first. Enterprise applications are to a large extent memory bound, that means the program execution time is proportional to the amount of memory accessed for read and write operations or memory being moved.As an example,

 we compare a full table scan of SAP’s accounting document line items table, which has 160 attributes,in order to calculate a total value over all tuples. In an experiment we did with 5 years worth of accounting of a German brewery, the number of tuples in this table was 34 million. In the underlying row database, 1 million tuples of this particular table consume about 1 GB of space.The size of the table was thus 35 GB. The equivalent columnstore table size was only 8 GB because of the more efficient vertical compression along columns.

If we consider that in real world applications only 10% of the attributes of a single table are typically used in one SQL-statement (see Figure 1), that means for the column store at most 800
MB of data have to be accessed to calculate the total values [1].


COLUMN STORAGE IS SUPERIOR TO ROW STORAGE WITH REGARDS TO
MEMORY CONSUMPTION:

Under the assumption to build a combined system for OLTP and OLAP data has to be organized for set processing, fast inserts, maximum (read) concurrency and low impact of reorganization. This imposes limits on the degree of compression for both row and column storage.

 While it is possible to achieve the same degree of compression in a row store as in a column store (see for e.g. IBM’s Blink engine [17]), a comparison of the two should be done assuming that the requirements above (especially fast inserts) are met,which excludes read-only row stores from the discussion.

In the column store, the compression via conversion of attribute values and the complete elimination of columns with null values only is very efficient but can be improved in this
research system by interpreting the values: all characters blank, all characters zero, and decimal floating point zero as null values. Applications think in default values and do not handle null values properly.

By translating the default values automatically into null values on the way into the database and back into default values on the way out. Comparing the memory requirements of column and row
storage of a table, the difference in compression rate is obvious.Various analyses of existing customer data show a rate of 2 for (write-optimized) row storage on disk.

For further memory consumption estimates we use a factor of 10 based on compression in favor of column storage.  Column storage allows us to eliminate all materialized views (aggregates) and calculate them algorithmically on demand. The storage requirements associated with these aggregates vary from application to application. The multi-dimensional cubes typically used in OLAP systems for materialized roll-ups grow with the cardinality of the individual dimensions. Therefore a factor 2
in favor of column storage based on the elimination of redundant aggregates is a conservative estimate.

Horizontal partitioning of tables will be used based on time and tenants. The option to partition into multiple dimensions is very helpful in order to use different qualities of main memory and processor speed for specific dimensions.

Within the context of memory consumption the option to split tables into current data and historic data per year is extremely interesting. The analysis of customer data showed that typically 5-10 years of historic data (no changes allowed) are kept in the operational database. Historic data can be kept accessible but reside on a much cheaper and slower storage medium (flash memory or disk).
The current data plus the last completed year should be kept in DRAM memory on blades for the typical year over year comparison in enterprise systems. For the separation by time we use two time stamps, creation time and completion time. The completion time is controlled by the application
logic e.g. an order is completely processed or an invoice paid.

The completion date determines the year in which data can become historic, that means no further changes are possible. With regards to main memory requirements we can take a factor 5 in favor of column storage into account. It is only fair to mention a horizontal partitioning could also
be achieved in record storage. Should the remaining table size for the current and last years partition still be substantial, horizontal partitioning by the data base management may occur. Ignoring memory requirements for indices and dimension dictionaries, we can assume a 10x2x5 time reduction
in storage capacity (from disk to main memory). Next generation boards for blade servers will most certainly provide roughly 500 GB of main memory with a tendency of further growth. Since arrays of 100 blades are already commercially available, installations with up to 50 TB for OLTP and OLAP could be converted to an in-memory only system on DRAM. This covers the majority of e.g. SAP’s Business Suite customers as far as storage capacity is concerned.