Free Space by Unload Columns or Alter Table for hidden concat_attribute columns

[Former Member]

I am trying to remove the concat attribute columns created by the join.  Tried these but didn't work

1) I can't seem to execute a unload syntax for a specific column, I can for a table but I can't do it for ANY columns let alone the hidden columns / concat_attribute.   UNLOAD <table_name> (<column_name>, ...)

2) ALTER TABLE <table_name>  <drop_column_clause> ::= DROP ( <column_name>[{, <column_name>} ...] ) I am typing in ALTER TABLE T_EMP DROP ($ENAME$DEPTID) this is not working on the hidden columns / concat_attribute but it is working on other columns.

Please see the screen shots of the SAP documention and the tests.

[former_member182114 · Accepted solution]

Hi Ram Bajaj,

About the UNLOAD I also find no option to unload a specific column.

For the drop you can use this command:

alter table "ERQ2QAS"."T_EMP" with parameters('DELETE_CONCAT_ATTRIBUTE' = '$DEPT_ID$ENAME$');

* you must keep the concat field within quotes, here simple.

Do not delete these concat as they are system generated:

$rowid$

$trex_udiv$

$trexexternalkey$

More info about this on this discussion: http://scn.sap.com/message/14317998

Regards, Fernando Da Rós

[Former Member · Accepted solution]

Fernando Thank you yes it worked.  Is here a way to stop these from getting created?

[Former Member · Accepted solution]

Fernando, can you tell me please?

1) when these get filed $trex_udiv$ and $trexexternalkey$ and why?

2) is there a way so no dymanic customer concat get created or if so drop off automatically?

[former_member184768 · Accepted solution]

Hi Ram,

Refer to the excellent presentation by explaining your questions in great details here.

Regards,

Ravi

[lbreddemann · Accepted solution]

Hi again Ram,

as this kind of question does get asked a lot when people first learn about concat_attributes and system columns, I think it's better to answer properly to it.

It's of course understandable that you want to save memory where ever you can.

And columns, that you up until recently didn't know about, seem to be the perfect technical option for that.

It's a no-brainer, right? Just get rid of these hidden columns that nobody can make use of and free a lot of precious memory...

WRONG!

These additional columns are - in the vast majority of cases - absolutely necessary to make SAP HANA work. There are an integral part of core features like DELTA MERGE or data model consistency.

Think of other DBMS. Technically, you could argue that all the secondary indexes just take space and shall be deleted. Do you do that? Of course not, as they are required for proper operations of the system.

So, these structures are there for your benefit. With them in place you get what you paid for: a blazing fast SAP HANA database.

Without them, you don't get this set of features and performance.

That's why you want them.

But knowing about them and how they work might lead to better design decisions for your data model.

Multi-Column join just because of the client column (MANDT)?

You could change that to single column join (= no concat_attribute) + filter on MANDT for each table (or just rely on the automatic client filtering in the information models).

Decision to go for a surrogate ID or a natural primary key consisting of 10 columns? You now know more to consider in this decision.

- Lars