Good afternoon, colleagues. In this article, I would like to touch on the topic of Row tables. For many database administrators, this type of table has long been the most natural type, so to speak, the default. Tables of the COLUMN type were mainly found in data warehouses, that is, databases with a predominant load of the OLAP type.
The main idea of SAP engineers when developing the HANA database was to unite the two worlds of OLTP and OLAP applications. As a result, tables with columnar storage in the HANA database have become the default tables, but despite the advantages of columnar tables in a large number of scenarios, the HANA database continues to use string tables. The features of using this type of tables will be discussed in this article.
Row tables are stored in a special area of memory called shared memory. When the database is started, this area is completely loaded into memory and remains there as long as the database is operational.
In Row tables, all data is placed in rows one after another, which makes it easier to access all the rows of the table. But with access to all column values, it is a little more difficult, since these values cannot be transferred from main memory to the CPU with the same efficiency as in the case of storing by columns. Compression of data with this type of storage is also less efficient.
The classic approach for relational databases, where data is stored in a tabular format, is storage that resembles the logical structure of a table. Each record is stored as a concatenated chunk of the values of each column in the table. Below is a table with the classic storage type.
, , . DBA.
, , . . CPU , where .
(main memory) CPU, , , . , , , ( ) .
HANA, Row-store :
, .
,
, .
row-store , : b-tree cpb+-tree ( b-tree) – . , , . SAP HANA cpb+-tree string, binary string, decimal. , b-tree . row-store , ( ).
(Multiversion Concurrency Control)
Multiversion Concurrency Control (MVCC) , . , . MVCC . , .
-.
row store , , commit. , undo. M_UNDO_CLEANUP_FILES -.
Garbage Collector. commit, ( ). , ( commit rollback). , ( commit), , (main memory), . , 8 . .
( M_SERVICE_THREADS), Thread Type “MVCCGarbageCollector”. , , , , THREAD_TYPE=’SqlExecutor’ THREAD_METHOD=’CommitTrans’.
Row-store
Row-store . , HANA 1.0, . , .
Row store 64 . . row-store , . , .
. row store . , . SAP Row Store , 10Gb 30%. HANA ONLINE OFFLINE.
ONLINE HANA 1.0 SPS8. Row store. SAP HANA 2.0 SPS3 , , , , . SAP HANA 2.0 SPS4 , , . SAP online , . , , OFFLINE .
HANA 2.0 SPS4, SAP OFFLINE , . OFFLINE , . 247 row-store . , , OFFLINE , .
! OFFLINE HANA.
SAP HANA 2.0 SPS4 ONLINE . row store. 60% row-store, . , . 2789255 - Automatic Online Row Store Reorganization.
This concludes my brief overview of tables with the Row storage type. Despite the advantages of columnar tables, string tables continue to be used in HANA database. Basically, such tables are used as technical or configuration tables with a relatively small number of records, while the main role is given to tables with a columnar storage type.