SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Overview
+
-
SAP HANA Architecture
+
-
SAP HANA Studio
+
-
Reporting in HANA
+
-
SAP HANA Text analysis
+
-
SAP BW on HANA
+
-
Miscellaneous
+
-
SAPUI5

Column Vs Row Data Storage

In the article An insight into SAP HANA Architecture we explained the basic architecture of SAP HANA.
In this article we will learn about column data store and row data store in HANA.
Overview of Row Data Storage and Column Data Storage

Relational databases typically use row-based data storage. However Column-based storage is more suitable for many business applications. SAP HANA supports both row-based and column-based storage, and is particularly optimized for column-based storage.

As shown in the figure below, a database table is conceptually a two-dimensional structure composed of cells arranged in rows and columns.

Because computer memory is structured linearly, there are two options for the sequences of cell values stored in contiguous memory locations:

Row Storage - It stores table records in a sequence of rows.
Column Storage - It stores table records in a sequence of columns i.e. the entries of a column is stored in contiguous memory locations.

Traditional databases store data simply in rows. The HANA in-memory database stores data in both rows and columns. It is this combination of both storage approaches that produces the speed, flexibility and performance of the HANA database.
Advantages of column-based tables:

Faster Data Access:
Only affected columns have to be read during the selection process of a query. Any of the columns can serve as an index.

Better Compression:
Columnar data storage allows highly efficient compression because the majority of the columns contain only few distinct values (compared to number of rows).

Better parallel Processing:
In a column store, data is already vertically partitioned. This means that operations on different columns can easily be processed in parallel. If multiple columns need to be searched or aggregated, each of these operations can be assigned to a different processor core

Advantages and disadvantages of row-based tables:

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).
    • Neither aggregations nor fast searching are required.
    • The table has a small number of rows (e. g. configuration tables, system tables).

Row based tables have dis-advantages in the following circumstances:
    • In case of analytic applications where aggregation are used and fast search and processing is required. In row based tables all data in a row has to be read even though the requirement may be to access data from a few columns.

Which type of tables should be preferred - Row-based or Column-based?

In case of analytic applications where aggregations are used and fast search and processing is required row-based storage are not good. In row based tables all data in a row has to be read even though the requirement may be to access data from a few columns. Hence these queries on huge amounts of data take a lot of time.

In columnar tables, this information is stored physically next to each other, significantly increasing the speed of certain data queries.

The following example shows the different usage of column and row storage, and positions them relative to row and column queries. Column storage is most useful for OLAP queries (queries using any SQL aggregate functions) because these queries get just a few attributes from every data entry. But for traditional OLTP queries (queries not using any SQL aggregate functions), it is more advantageous to store all attributes side-by-side in row tables. HANA combines the benefits of both row- and column-storage tables.

Conclusion:
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.


Continue reading:


Support us by sharing this article.



Explore More
Close X
Close X

15 thoughts on “Column Data Storage and Row Data Storage – SAP HANA

  1. Madhusudana Rao Koleti says:

    Very nice site , it has step by step guidance , people who interested to learn HANA self this is preferrable site

  2. mani says:

    Nice blog…looking for more Information

  3. Srini says:

    Great site!, information is crisp & concise.
    Thanks for the efforts.

    –srini

  4. Shwetank Kumar says:

    Very Well presented!!! Treat to read all information, very crisp and to the point.

  5. Suresh says:

    Good place to start HANA. I have enrolled myself for HANA certification, and I didn’t know from where should I start. Then I googled alot and finally I reached here. Started preparing. This site helped me to understand very basic things about HANA. Ofcourse, the basement is very important for every building! And finally now, am a SAP Certified Associate! Thanks! 🙂

    • Admin says:

      Hi Suresh,
      Thanks a lot for the valuable feedback and congrats. Good luck for your future.

      Regards,
      Admin

  6. johnny says:

    you forgot to mention the most prevalent problem of columnar databases – which is the overwhelming cost and overhead of updating columnar data.
    the biggest disadvantage of columnar databases is the expensive cost of updating. when you update a row you have to move lots of data.
    that is why pure columnar databases are used only for analytical processing and fail for dml transactions

    • Eswar says:

      Hi johnny,

      The columnar data updation is not a problem in HANA. It can be achieved through delta merge concept in HANA.
      Please read about Delta Merge concept. Hope this helps.

      Thanks..

  7. Narendra says:

    Nice information and its very use full for beginners like me … Thank you so much…

  8. Pedro López says:

    Nice and clear explanation about storage type column store and row store !!

    Best regards.

    • Admin says:

      Hello,

      Thanks for the appreciation. 🙂

      Email us for any query at “admin@saphanatutorial.com”.

      Thanks,
      Admin

  9. varun says:

    Wow really worthy site. Concepts are very clear, understandable and well maintained. Like to learn more…. 🙂 🙂

    • Admin says:

      Hello,

      Thanks for the appreciation, for any query you also could email us at “admin@saphanatutorial.com”.

      Thanks,
      Admin

  10. Ritt says:

    Hello,
    Can you give a real time example of Row based Table?
    Means in which scenario we used it?

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy