Recommendations for use of Row Store Tables

[former_member204796]

Hi ABAP on HANA Gurus,

I think by this time, most of us are aware of the difference between Row Store and Column store in SAP HANA ( Column store actually uses an internal memory layout, which is highly optimized for column operations such as search and aggregation.)

Recommendation from SAP is to always use Column store for tables with Business Data (i.e. Master Data and Transaction data ) within SAP HANA and Row Store to be used very selectively for Application server system tables. I have these queries :

1. What kind of system tables are referred here ?

2. Can we use Row store for Configuration tables ? In this case, we can optimize the query by using SELECT SINGLE * for required condition.

3. For Side car scenarios, where in we may have SAP ERP on Classical d/b and required Tables/data are replicated to HANA by SLT, then I understand that SLT would automatically decide which tables will be Column Store and Row store.

4. Are the recommendations different for Suite on HANA ? I think a Hybrid Row and Column store is used here to provide optimizations in the OLTP system.

5. So, what is the approach to be followed when we are doing any Native HANA development and we need to create few Custom tables. Although by default, they need to be Column store, are there any special cases to be considered for Row store ?

Thanks,

Suma

[Former Member]

Hello Suma,

• What kind of system tables are referred here ?

Tables like TRDIR, DD02L etc. where attributes of meta data are stored
 

• Can we use Row store for Configuration tables ? In this case, we can optimize the query by using SELECT SINGLE * for required condition.

Yes. In the case of configuration tables, number of records are few  and all the fields are normally queried. So I think SELECT SINGLE * should not be a problem.

• For Side car scenarios, where in we may have SAP ERP on Classical d/b and required Tables/data are replicated to HANA by SLT, then I understand that SLT would automatically decide which tables will be Column Store and Row store.

In SLT transaction IUUC_REPL_CONTENT, we can set the target table storage type as raw store. The default will be column store.

• Are the recommendations different for Suite on HANA ? I think a Hybrid Row and Column store is used here to provide optimizations in the OLTP system.

Correct. Storage attributes of all the standard tables will be set by SAP during migration. For custom tables, we can update it in Technical settings. I think it is column store by default here also.

• So, what is the approach to be followed when we are doing any Native HANA development and we need to create few Custom tables. Although by default, they need to be Column store, are there any special cases to be considered for Row store ?

The custom table used for storing few records of non-numeric attribute data may be created as raw store. All others should be column store.

Thanks,

Venu

[sundaresan_k]

Dear Suma,

Just few additional points to what Venu Cherupillil has already covered.

The approach for deciding between column store and row store would be as follows

Column Store would be the preferred storage type when your access to the table would be of of the nature where

1. there are several column based operations done on huge volumes of data records
2. the data model of the table tends to have large number of columns and and majority of the columns are unused

Row Store would be the preferred storage type when

1. The table mainly contains distinct values(which leads to a very less compression rate)
2. The access from your application code typically involves fetching all or most of the columns together and the type of access does not involve aggregations or search
3. The table is mainly meant for storing unstructured data
4. Typical volume of records in the table is very minimal

Since you also specifically mentioned about HANA Native Development I assume you would have fair control over the data model and hence the above pointers might help you in choosing the right storage type.

Hope this helps.

Best regards

Sundar