Let’s do this quick and painless!

From SPS 7 (rev. 70.00) SAP HANA doesn’t automatically create concatenated attributes for multi-column-joins anymore.

BANG!

There you go.

Just asked for it ( Looking for more info on CONCAT_ATTRIBUTEFree Space by Unload Columns or Alter Table for hidden concat_attribute columns… ) – just briefly after learning that these concat attributes were there in the first place –  and now you got it.

Let’s see how much better the HANA world will be now.

Of course everything comes with a price for it.

Multi-column-joins that are executed without these concat attributes will take more time compared to the cases where a concat attribute can be used.

In order to have concat attributes in place when you want them, you can simply create indexes on the tables covering the exact set of columns.

For multi-column joins defined in any of the information model views the activation of the view will still create the creation of the concat attribute and that makes sense.

All in all the big problem with these concat attributes was that they had been created automatically and didn’t require any privilege for that.

Even in production systems one could fire a SQL statement with a join that would in turn lead to the automatic creation of two relatively large data structures.

On top of that these concat attributes where difficult to find and remove.

With the current setup this cannot happen any longer.

Nice.

Once I have more time again, I will provide an update/follow up to Playing with SAP HANA that provides more details and explanations.

But that’s all for now folks.

Now you know!

Have a great holiday season, cheers!

Lars

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Justin Molenaur

    Just was discussing with a client that CONCAT_ATTRIBUTE would no longer be needed when they upgrade to rev74. I tested with some direct SQL statements and then information views and found that the information views still do create the CONCAT_ATTRIBUTES.

    Then of course I re-read this post more closely and saw this.

    >>For multi-column joins defined in any of the information model views the activation of the view will still create the creation of the concat attribute and that makes sense.<<

    So it seems that the protection is only for SQL issued commands. Now I know I need to read Lars a little more closely at times… 🙂

    Regards,

    Justin

    (0) 
    1. Lars Breddemann Post author

      Hi Justin,

      yes, the concat attributes will still be created for information models. The rationale here is: creating the information model is an active decision and protected by permissions. So not everybody can just go and do that and thereby creating lots of concat attributes that eat up space.

      But this was exactly the case with SQL users that just “played around” a bit with join conditions in ad hoc SQL queries.

      For that kind of queries a slightly slower performance seems to be acceptable compared to potentially massive space requirements through accidentaly created concat attributes.

      Cheers,

      Lars

      (0) 
      1. Sagar Joshi

        Hi Lars,

        Are you aware of any further changes in SP8?

        I recently checked by creating attribute views with joins on multi-columns and dont see concat attributes created.

        Thanks

        Sagar

        (0) 
  2. Martin Fu

    we are using rev84, some how the concatenate attribute is still created by the system automatically, is there a parameter for disabling it?

    (0) 
    1. Lars Breddemann Post author

      For that rather old version there is no officially supported parameter available.

      Moving on to a current SAP HANA revision is recommended anyhow, so that would be just another good reason to do so.

      (0) 
        1. Lars Breddemann Post author

          Ah sorry  – misread it and thought you were still on rev. 64…

          With SP8 many of the automatically generated concatenated columns are actually not created anymore.

          As you still experience that they are automatically created, it’s required to look into this in more detail.

          Please have a support incident opened for that.

          – Lars

          (0) 

Leave a Reply