How column wise read and delete is done for delta data ?

[pavneet_rana]

Hi to all,

What is know is that HANA is optimized for column wise insert.

I have one doubt.

Example: i have 2 field in table1 in HANA,and stored record in column wise.

NAME    Marks

T             20

C             30

now if Name T, marks changes from 20 to 30, so as its column wise storage, so there will be no update, but new record will be inserted for T to table1.

NAME    Marks

T         20

C        30

T         30

but if i will read table1 for name T, i will get T 20 and T 30 as record, which is not be correct, as latest record will be T 30.

So how SAP HANA will read the correct record for such cases ?.

Also how delete will work for column wise storage ?.

I shall be thankful to you for this.

Regards

Pavneet Rana

[Former Member · Accepted solution]

You are overthinking this. HANA is an ACID compliant database and its column store operates just like a regular database.

HANA operates MVCC to ensure that read/write operations return consistent results even with many readers/writers.

Under the scenes, the column store is insert-optimized, so the HANA database does a delete and insert rather than an update. As a user, you don't need to worry about that - HANA takes care of it.

[pavneet_rana · Accepted solution]

Thanks for reply.

i have some small doubt below.

1. So it mean for column up-dation is not done in HANA, insertion for existing record rather than up-dation, to optimized the column operation.

2. please can you explain the below example.

Example: i have 2 field in table1 in HANA,and stored record in column wise.

NAME    Marks

T             20

C             30

now if Name T, marks changes from 20 to 30, so as its column wise storage, so there will be no update, but new record will be inserted for T to table1.

NAME    Marks

T         20

C        30

T         30

but if i will read table1 for name T, i will get T 20 and T 30 as record, which is not be correct, as latest record will be T 30.

So how SAP HANA will read the correct record for such cases ?.

Also how delete will work for column wise storage ?.

3. please can you provide some link which tells about column based operation done internally in HANA.

Regards

Pavneet Rana

[Former Member · Accepted solution]

HANA is a database. If you send INSERT it will insert:

NAME    Marks

T         20

C        30

T         30

if you send UPDATE it will update.

NAME    Marks

T         30

C        30

HANA is quite complex internally - but all this complexity is hidden to the end-user. You can read more about it here:

Amazon.com: SAP HANA Essentials eBook: Jeffrey Word: Kindle Store

John

[pavneet_rana · Accepted solution]

Thanks for reply,

So it mean the for column we can have both insert and delete in HANA.

if this is right.

i have read in SAP HANA book, that to optimized update, we do insertion rather than update for column operation.

if yes .

both 2 point contradict each other.

please correct me if i am misunderstood.

Regards

Pavneet Rana

[Former Member · Accepted solution]

Can I please ask you to read the "How to ask questions in SCN" article? You're not being specific or clear enough in your questions, and as a result I'm not being clear or specific in my answers.

As I said before, HANA is quite complex under the scenes. It is much faster for INSERT than it is for UPDATE. If you want to understand why this is, I suggest you read Jeff's book.

However, the impact of this is that mass updates can be quite inefficient. It is often more efficient to delete and re-insert data.

In the real world, data is not updated, it is created. Let's take your example

Name     Marks

T           20

C          30

Now what happened next? You need to think about the representation of data. Let's say these were for example SCN scores, and T just got another 10 marks.

Name     Marks     Timestamp

T           20          09:00:00

C          30           09:05:00

T          10           09:06:00

Now if we want to get the total then we do SELECT Name, SUM(Marks) from Table GROUP BY Name

This is the way that you would model most efficiently using HANA, rather than to use UPDATE statements.

[pavneet_rana · Accepted solution]

Thanks for reply,

I got it, it mean rather than update , HANA do insertion by (delete and insert the new record) for existing record. so HANA is optimized for column update operation.

Regards

Pavneet Rana

[rindia]

Hi Pavneet,

You have the wrong perception that there is no update for column storage. Instead we can perform CRUD (Create, Read, Update, Delete) on column storage too.

The main difference between column and row based tables is Column based tables must have the key while for row based it is optional.

Regards

Raj

[pavneet_rana]

Thanks for reply,

But i have read that, to optimized column update, HANA don't  support update operation, instead it will do insert the record rather update for column operation.

Am i right, please correct me if i have understood .

Regards

Pavneet Rana

[Former Member]

Raj this is a common misconception. Column-oriented tables do not need a key.

[rindia]

Hi John,

I do completely agree with you. I apologize for this.

When creating column based table using Import option, it will ask us to have a key. Might be due to this I had misconception but now I am clear.

Thanks John.

[Former Member]

I thought that had been fixed - that's a bug. There's also bugs in CDS (SP06, I didn't check SP07) and River that enforce the same constraint.