HANA SPS07 - Behavior change for internal concatenated columns?

[former_member185070]

Hi,

I attended TechEd this week ( which was very inspiring 🙂 ). It lead to a question regarding the upcoming SPS07-release of HANA.

Will there be a change in behavior within HANA on internal concatenated columns (used for instance with multi-column joins behind the screens - visible via M_CS_ALL_COLUMNS)?

Thanks in advance!

[lbreddemann · Accepted solution]

Hi Stijn,

as promised, here my reply to the questions.

For the foreseeable future, the concatenated columns will stay with the column store.

However, there are several improvements available already.

One option is only available for primary key concatenated attributes and basically doesn't store the concatenated values anymore but a HASH value which takes less space to store.

To create such a primary key, you need to provide the type with the ADD PRIMARY KEY statement:

alter table <table_name> add primary key inverted hash (<col_name1>, <col_name2>, ...) ;

This is already documented in the official SQL reference guide.

Concerning multi-column join without materialized concat-attributes, there are also development activities going on but the features had not been officially released yet. Thus, I can't say a lot about that right now.

Hope that answers your questions at least partly to your satisfaction

Cheers,

Lars

[former_member185070 · Accepted solution]

Hi Lars,

Thanks for the quick answer and the great insights you provide!

The info on the primary key is already very good info for the cases where it is appropriate to apply, I did not come across it yet. Just tested this on a table with 20 million rows and the gain in mem-size is significant.

On the multi-column join, we are currently on revision 67 and see they are still being created. I'm curious to see the outcome in SPS7 as they do affect memory-utilization quite a bit. A bit more patience 🙂

Thanks,

Stijn

[lbreddemann · Accepted solution]

One update on that:

Apparently the INVERTED HASH functionality is not yet fully supported/implemented in SPS 7 and made its way into the reference documentation by accident.

Therefore, don't try to use it for now.

- Lars