Conceptually, 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 exist as shown in figure 2.
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.
Column-based tables have advantages in the following circumstances:
* Calculations are typically executed on single or a few columns only.
* The table is searched based on values of a few columns.
* The table has a large number of columns.
* The table has a large number of rows and columnar operations are required (aggregate, scan etc.)
* High compression rates can be achieved because the majority of the columns contain only few distinct values (compared to number of rows).
Row based tables have advantages in the following circumstances:
* The application needs to only process a single record at one time (many selects and/or updates of single records).
* The application typically needs to access a complete record (or row).
* The columns contain mainly distinct values so that the compression rate would be low.
* Neither aggregations nor fast searching are required.
* 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.
Bluesky
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.