Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SAP HANA row and column store table
SAP HANA Modeler Views can only be created on the top of Column based tables. Storing data in Column tables is not a new thing. Earlier it was assumed that storing data in Columnar based structure takes more memory size and not performance Optimized.
With the evolution of SAP HANA, HANA used column-based data storage in Information views and presented the real benefits of columnar tables over Row based tables.
Column Store
In a Column store table, Data is stored vertically. So, similar data types come together as shown in the example above. It provides faster memory read and writes operations with help of In-Memory Computing Engine.
In a conventional database, data is stored in Row based structure i.e. horizontally. SAP HANA stores data in both row and Column based structure. This provides Performance optimization, flexibility and data compression in HANA database.
Storing Data in Columnar based table has following benefits −
- Data Compression
- Faster read and write access to tables as compared to conventional Row based storage
- Flexibility & parallel processing
- Perform Aggregations and Calculations at higher speed
Functional Difference - Row vs Column Store
It is always advisable to use Column based storage if SQL statement has to perform aggregate functions and calculations. Column-based tables always perform better when running aggregate functions like Sum, Count, Max, Min.
Row-based storage is preferred when output has to return complete row. The example given below makes it easy to understand.
While running an Aggregate function (Sum) in sales column with Where clause, it will only use Date and Sales column while running SQL query so if it is column based storage table then it will be performance optimized, faster as data is required only from two columns.
While running a simple Select query, the full row has to be printed in output so it is advisable to store the table as Row based on this scenario.
1K+ Views
To Continue Learning Please Login