Size of a compressed table in ECC vs Size of a HANA table

[Former Member]

Hi All,

We have compressed our tables in ECC where in we see dramatic reduction in the size - for example ANLP went down from 140 GB to 22 GB. However, we haven't noticed any change to its size in HANA (50 GB). Is it possible to get this reduction in HANA by some manner as it stands that the ECC table occupies less size than the HANA table ?

Thanks,

RM

[lbreddemann · Accepted solution]

HI Ramesh

sound like you have a data mart scenario here?

What kind of compression did you activate on the ECC system? What platform does it run on?

- Lars

[Former Member · Accepted solution]

Thanks Lars for your reply. We use HANA purely on its own as a sidecar scenario for AA RDS and we have some month-end close reporting using Business Objects.

The table compression was done using backend scripts, with 'for OLTP' option. We are on oracle DB on hp-ux. If you are after SAP application platform, then its 'SAPKNA7029' (Rel. 7 patch 29).

[lbreddemann · Accepted solution]

Hi Ramesh,

well, ok, in that case it seems that the table compression of your Oracle system yields better compression results.

For the SAP HANA side you can of course check whether the compression is set up optimally (UPDATE <table> WITH PARAMETERS ('OPTIMIZE_COMPRESSION' = 'FORCE')).

A compression factor of 2,8 (140:50) is actually not too impressive.

Another possible explanation for the size in SAP HANA could be that there are large hidden columns on the table that had been created in SAP HANA.

See my presentations and on that.

- Lars

[Former Member · Accepted solution]

Thats true Lars, the ECC compression seems far too much better, not so encourgaing from HANA perspective. Our compression on HANA side has been set up optimally. I did go through these presentations a while ago, the hidden columns are the biggest culprits actually across all our tables.

For the same table ANLP(50 GB in HANA):

$trexexternalkey$ occupies 27 GB and

Concat Attributes:

$ANLN1$ANLN2$BUKRS$     - 6 GB

$ANLN1$ANLN2$MANDT$     - 2.5 GB

$ANLN1$ANLN2$                    - 2.5 GB

$ANLN2$BUKRS$                   - 1.2 GB

Thats 40 GB out of the 50 by internal columns.

I know we can get rid of the concat attributes but they will get created again when the users run the reports again isn't it ? Do you know if there is a better way of compressing these HANA tables, not always an easy pill to digest when I look at the compressed size in ECC and compare it to HANA !!

[lbreddemann · Accepted solution]

You cannot get rid of the $trexexternalkey$ column, but typically you should be able to drop some of your concat attributes.

With a current SPS 08 system I would not expect to see many of them being recreated.

Another question is: did the Oracle compression value also include all indexes that are defined on the table?

[former_member184768 · Accepted solution]

Hi Ramesh,

I am sure Lars will have the right explanation on the table, especially on the size of the $trexexternalkey$ column, but I think it is quite high. It seems to be more than the table size itself. Could you try to create another table similar to your table, but without the primary key and check the table size.

Even I am eagerly waiting for Lars to comment on this issue. It is quite interesting to know why the size is so big for the $trexexternalkey$.

Regards,

Ravi

[Former Member · Accepted solution]

No Lars, the Oracle compression value is just for the table itself (without including the indexes).

@Ravi: Thanks for your comment too. Our dev box is quite small to do the test mentioned by you. I will check other big tables and let you know if am able to succesfully create another replica to check the size.

[lbreddemann · Accepted solution]

It's not that mysterious.

It's all about uniqueness and the options for compression that this leaves.

Suppose a table looks like this (6 rows):

GROUP	THING
A	1000000001
B	1000000001
A	3000000001
A	4000000001
B	3000000001
C	1000000001

This makes three distinct values for GROUP (compression factor 3:6) and 4 distinct values for THING (4:6).

Column $trexinternalkey$ is the concatenated primary key plus length indicator.

So for the first row it could look similar to this: 1;A;10;1000000001

This is 17 bytes to store for each row in the table, where as the record itself takes one byte for the GROUP and 8 byte for the THING value (if we assume a integer value here). That's 9 bytes for the original values, but 17 for the key.

On top of that the key values are unique and cannot be compressed by the dictionary encoding.

That's why the $trexexternalkey$ can become larger than the columns included.

Cheers,

Lars

[lbreddemann · Accepted solution]

In that case you're comparing apples and oranges.

The concat attributes effectively are indexes and for proper size comparison you have to include the index structures of the source system as well.

After all, you cannot run the application without those structures in the source system

The only real annoyance (and that had been removed to a large degree) is the automatic creation of those concat attributes without letting the user know.

- Lars

[Former Member · Accepted solution]

Thanks Lars. That does help a lot and clear the doubts !!