HANA column store backgrounds (INSERT ONLY)
We’ll start with some background information of the column store that will help us to better understand the guidelines
introduced by Eric Westenberger.
It is often mentioned that SAP HANA uses an „insert only“ approach. What does this mean for you as ABAP
developer? Does this mean that UPDATE or DELETE statements cannot or should not be used anymore?
In this post we will take a look under the hood and explain the consequences.
Before we start looking into the technical details, let us give the short answer to the question above:
No, as an ABAP developer you still should use INSERT, UPDATE and DELETE as intended in the application.
The HANA insert-only approach works behind the scenes with immediate benefits. However, understanding the
way how HANA works can be exploited in new designs in particular when it comes to new applications dealing
with large data sets.
Now, let’s pull up the sleeves and dig into the details of the column store within SAP HANA:
HANA is insert only. What does this mean to you as an ABAP developer?
In the column store HANA will only insert data. That is whenever you update or delete data HANA will
just insert. For performance reasons, the column store is devided in a main and a delta part. New data is going
to the delta and will be merged frequently to the main. Changing statements work as follows:
An INSERT statement will just insert a new record in the delta.The merge process will move the record from
delta to main.
A DELETE statement will select the record and mark it as invalid by setting a flag (for main or delta). The merge
process will delete the record from memory once there is no open transaction active for it anymore.
An UPDATE statement will insert a new version of the record. The merge process will move the latest version from
delta to main. Old versions will be deleted once there is no open transaction active for them anymore. The update
therefore consists internally of an insert + delete.
The following picture illustrates this principle schematically. Please note that this picture does not reflect the real
storage in column store. You can see a deleted record in main (id 7) and 2 old versions (marked in grey) of
records (id 8 and 14) where the name has been updated, which lead to an insert of a new version of the records.
The attached program demonstrates insert only by inserting, updating, deleteing data and reporting record
counts. As you can see while the logical record count does not increase when you do an update the
raw_record_count does. This is insert only in action.
What does this mean for you, the ABAP developer? Should you change the way you work with
INSERT, UPDATE and DELETE?
As we said in the beginning: No, not at all. Let HANA do the insert-only and use INSERT, UPDATE and
DELETE just the way you are used to.
And what are the benefits of the insert only approach?
Well, since the main store is compressed (which is good for reading performance, detailes in the next blog)
changes would be expensive. Just imagine you want to make a change in a compressed text file. You need
to uncompress, make the change and compress again. For this reason data is only appended to the
uncompressed delta store where change operations are much cheaper. The merge moves the data from
delta to main and thus compresses only from time to time and not with every change. Setting the deletion
flag is based on rowids and works for compressed and uncompressed data.
In the next blog we will dive a little bit deeper in the column store and discuss the main and the delta in more