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
Course Index
Close
X
SAP HANA Basics
Course Overview
1. Introduction to SAP HANA
2. SAP HANA Architecture
3. SAP HANA Studio
4. SAP HANA Modeling
5. Reporting on SAP HANA
6. What is Next?
<< Previous
Next >>

2.3 Row Store Vs Column Store

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 disadvantages in case of analytic applications where aggregation are used and faster search & processing are required. In row based tables all data in a row has to be read even though the requirement may be there 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 faster search & processing are required, row-based storage are not good. In row based tables all data stored in a row has to be read even though the requirement may be there to access data from a few columns. Hence, these queries on huge amounts of data would take lots of times.

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

The following example shows the difference between the 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.

Connect to The SAP HANA Forum to ask any question, provide feedback or share information.

Click here to go to Next Chapter.

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