Column Store vrs Row Store

[Former Member]

Hi everyone!

At defining a new table in the hana studio, you can decide if you want it to be stored with column store or row store.

What is the criteria to decide that?

If there is such option, I don't think every table would be column, right?

thanks,

Alejandra Meraz

[former_member114166]

When you use a Row Store Table in an Analytical View, you get the following error:

ViewTest:"SCHEMA.MYROWTABLE" is not a COLUMN STORE table.

By creating a Calculation View (SQLScript), you can workaround this.

The following SQL snippet worked for me!

/********* Begin Procedure Script ************/

BEGIN

            var_out = select Col1, Col2, Col3 FROM  "MYSCHEMA"."MYROWTABLE" ORDER BY Col3;

END /********* End Procedure Script ************/

[former_member123979]

I was reading this paper from Vishal Sikka on Hana

http://delivery.acm.org/10.1145/2220000/2213946/p731-sikka.pdf?ip=203.13.146.60&acc=ACTIVE%20SERVICE...

It seems there is support for both row as well as column store abstracted through a common interface,

Each record has a lifecycle where it gets started stored into a row store and moves gradually to a column store in an async fashion.

So both OLTP and OLAP can be supported directly from the same store.

[Former Member]

Hello,

SAP HANA 1.0 supports both row store and column store.

Column store is optimized for analytic purpose. Currently, in HANA 1.0 modeling only possible for column store tables.

It provides high performance of read operation and good peformance of write operations.It also provides high compression rate.

Row store :More optimized for write operations, less compression rate and performance will be less compared with column store.

Another aspect is, row store tables are loaded into memory during startup. Column store tables are set as a default to be loaded on demand i.e. the system loads columns when necessary.

Regards,

KK

[sachin_kulshrestha2]

Hi,

System tables used for functioning of database will obviously be row store. As mentioned Data table with larger volume of data could be created as column store.

Hope this helps!!!

Sachin K

[Former Member]

Column Store: which tables are using for Analytic purpose like Dimension, Cubes, because for grouping and column functions.

Row Store: it is actually OLTP purpose and like common RDBS tables for the purpose of OTLP transactions.

At the moment SAP Presenting HANA DB as Analytical purpose, so that we deal most of Column tables.

Difference between that you can more information

http://db.csail.mit.edu/projects/cstore/abadi-sigmod08.pdf

Thanks

Rao

[Former Member]

Using the SQL editor you can create both row store as well as column store tables.

create table >> row table

create column table >> column table.

Going forward every tables in HANA should be column store.

Best Regaeds

Pranav.

[lbreddemann]

> Going forward every tables in HANA should be column store.

Hmm... if every table should be a column table, we would not have build in two storage options, two optimizers and the connecting bit and pieces. We would have just put the column store in HANA.

But there are both options for a reason.

Each of them got it's own specifics that might suit your use case better than the other.

So, with both options available you can choose which one is better for your application.

regards,

Lars

[Former Member]

my understanding is SAP HANA 1.0 support only Column Store.

[lbreddemann]

That's plain wrong.

You can use both stores also in version 1.0.

Anyhow, what should be obvious (in my thinking at least) is that you cannot build a COLUMN VIEW upon a ROW STORE table.

regards,

Lars

[Former Member]

Aljendera,

If you have row-store table in HANA, you cannot create any views on top of those tables. Any view on ROW_STORE table errors out saying something like invalid column view: row table is not allowed: To best of my experience the table has to be "COLUMN_STORE" if you want to build views on top of it.

By default with create command it creates row table. I am not aware of anyways to translate row-store to column store. Based on my personal experience, the only way it works is creating column table with CREATE COLUMN table and transfering data into COLUMN table.

If you are using DataServices, it provides you an option to pick COLUMN table as target to HANA.

The other major differentiation is size of the table. Usually table with less volume (like all system tables) are created as row-store where as all large volume tables are stored as COLUMN STORE.

[Norman_May]

Hallo,

using CREATE VIEW v_name as <SQL-stmt> you can also create standard SQL views on top of row store tables.

Regards, Norman

[rajarshi_muhuri]

but looking from the systax , it seems that view from row table will be a row oriented view ?

as such i opened a new thread on this

[Former Member]

Hi

As far as I remember it will not allow you to pick, it decides itself

Cheers

Tansu

[lbreddemann]

Hello Alejandra,

when considering which data store to choose (which, by the way, can be changed later on as well), you have to take into account:

• will you usually need the complete row (all columns)? If so, row store may be more efficient, as reconstructing the complete row is one of the most expensive column store operations.

• will you need to join the row-store table to a column store table? If so, you should avoid using a different storage type, since using both storage engines in a statement leads to intermediate result set materialization which is another name for bad performance.

• do you want to fill the table with huge amounts of data, that should be aggregated and analysed? If this is the case, the column store is the better option.

As a rule of thumb you may just start with column-store tables and change them to row-store tables when you encounter performance issues.

In general most developers cannot anticipate all important use cases for the tables they design.

This is especially true for living and growing systems.

So, more important than choosing the 'right' storage in the beginning is to monitor the performance and to benchmark the differences when changing the storage engine.

Best regards,

Lars

[Vitaliy-R]

I never actually tested this, so you need to check yourself, but I was told that HANA modeling views can be only created on columnar tables. -VR