Question Re: Understanding Row Store & Column Store? in Technology Q&A

Understanding Row Store & Column Store?

SridharKarra — Fri, 11 May 2012 00:46:17 GMT

Hello Experts,

Can someone make me understand the attachments regarding row store & column store? I need to understand in each of the areas how the requests is processed by each component.

All help appreciated.

Thanks,

Sri K.

Re: Understanding Row Store & Column Store?

Former Member — Mon, 21 May 2012 05:10:31 GMT

Hi Sridhar,

Could you attach the images again - both JPGs don't show...

--Juergen

Re: Understanding Row Store & Column Store?

Former Member — Mon, 21 May 2012 13:31:29 GMT

Hi Sridhar,

Row based tables store a table in a sequence of rows and the benefits of this is

a) easier to insert and update

b) the data is stored together

The disadvantages are

a) all data in a row has to be read even though the requirement may be to access data from a few columns (in analytical processing)

Column based tables store a table in a sequence of columns and the advantages are

a) Only affected colums have to be read during the selection process of a query.

b) Any of these columns can serve as an index

The disadvantages are

a) After selection selected rows have to be reconstructed from columns.

b) There is no easy way to insert / update.

The other advantage is column stores are easier to compress, by working with dictionaries where the attributes are represented in dictionaries using integer values.

Re: Understanding Row Store & Column Store?

SridharKarra — Fri, 25 May 2012 14:52:44 GMT

http://scn.sap.com/docs/DOC-13231

Thanks,

Sri.

Re: Understanding Row Store & Column Store?

Former Member — Thu, 07 Jun 2012 12:37:21 GMT

Hi Sridhar,

Row Store – Traditional Relational Database, the difference is that all the rows are in memory in HANA where as they are stored in a hard drive in traditional databases.

Column Store – The data is stored in columns like in SAP BWA.

Re: Understanding Row Store & Column Store?

former_member184871 — Thu, 07 Jun 2012 13:23:29 GMT

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.

Re: Understanding Row Store & Column Store?

Former Member — Wed, 13 Jun 2012 15:20:54 GMT

Please read the research paper published by Hasso Plattner, on why In-Memory Column database is superior for performance.

http://www.sigmod09.org/images/sigmod1ktp-plattner.pdf

Re: Understanding Row Store & Column Store?

Former Member — Fri, 20 Mar 2015 12:09:47 GMT

Hi Guys,

I have small doubt here. Can some one please explain me, how data is fetched from column store and how it increases the speed of fetching the data ?

I know that, data is stored in column wise. But i didn't uderstood how data is connected for a single record in column store and how it reads fastly?.

Re: Understanding Row Store & Column Store?

lbreddemann — Fri, 20 Mar 2015 12:27:58 GMT

There are meanwhile several options available to learn about this.

A convenient way e.g. is Richard's and my book .

- Lars

Re: Understanding Row Store & Column Store?

Former Member — Mon, 20 Jul 2015 22:47:47 GMT

Very Well Explained here : SAP HANA Academy

https://www.youtube.com/watch?v=xO-DwghE6N0

Thanks,

Uday

Re: Understanding Row Store & Column Store?

praveenkm — Tue, 17 May 2016 11:39:51 GMT

This message was moderated.