Alter table type from COLUMN to ROW

[rajarshi_muhuri]

TABLE type can be changed from ROW to COLUMN (and vice versa) using the ALTER TABLE command .

Lars Breddemann wrote

when considering which data store to choose (which, by the way, can be changed later on as well), you have to take into account:

* will you usually need the complete row (all columns)? If so, row store may be more efficient, as reconstructing the complete row is one of the most expensive column store operations.

* will you need to join the row-store table to a column store table? If so, you should avoid using a different storage type, since using both storage engines in a statement leads to intermediate result set materialization which is another name for bad performance.

* do you want to fill the table with huge amounts of data, that should be aggregated and analysed? If this is the case, the column store is the better option.

As a rule of thumb you may just start with column-store tables and change them to row-store tables when you encounter performance issues.
In general most developers cannot anticipate all important use cases for the tables they design.
This is especially true for living and growing systems. 
So, more important than choosing the 'right' storage in the beginning is to monitor the performance and to benchmark the differences when changing the storage engine.

So suppose we have a COLUMN table , but would be requiring to get data from many columns (so would be a very expensive column operation) , would it be advisable to change the table type FROM COLUMN to ROW on the fly . would this be a resource intensive operation if the table has a lot of data ?

Lets suppose , if the above can be done , but there exists a interdependency for the column table (say from another simultaneous operation) , and thus should remain as COLUMN table as such . so what would be the better option in this case .

Creating views is not an option as it seems from the SQL guide , that there was not an option to create a ROW view from a COLUMN table. ?

Edited by: Rajarshi Muhuri on Nov 27, 2011 3:25 AM

[Former Member · Accepted solution]

Yes I heard that row tables load into memory when start the database. That means Column tables also stored in the memory.

Ok I explain this, first time you started database all row tables move to memory, not column tables. When you play with any AV, CV related column tables then that data move to memory, according like that all data one bye goes to memory. ( this one I heard some video but I donu2019t proof that, if itu2019s wrong let it be confirm others, but I guess itu2019s right 

Thanks

Rao

Edited by: Vigneswararao on Nov 27, 2011 6:21 PM

[tomas-krojzl · Accepted solution]

Hello,

this one I heard some video but I donu2019t proof that, if itu2019s wrong let it be confirm others, but I guess itu2019s right

I am in exactly same situation - I am also sure I heard/saw is somewhere but now when looking for some proof I am not able to find anything.

Tomas

[rajarshi_muhuri · Accepted solution]

you guys are watching too many HANA movies and listening to HANA gossip

I am waiting to watch HANA 2.0 in 3D

but ofcourse I am sure that by that time SAP will change its name to something else

[lbreddemann · Accepted solution]

Ok, just to clarify this point:

ALL data HANA works on needs to be in memory.

For ROWSTORE tables, the indexserver loads the data during startup automatically to memory.

These tables also need to stay in memory (the cannot be unloaded).

For COLUMNSTORE tables the situation is different. Every column will be loaded to memory upon first usage and - depending on memory usage situation - unloaded again.

It's not at all difficult

regards,

Lars

[tomas-krojzl · Accepted solution]

Hello Lars,

thanks for confirmation - I started to be afraid that it is just rumor I overheard somewhere because I was not able to find this piece of knowledge anywhere in document...

Tomas

[Former Member]

Dear Rajashri,

1. you cann't alter table from column to row using alter command.

• but you can achieve this through Stored procedure, just Little bit HSQL coding.

I hope upcoming versions SAP Gives like following SQL statements ( following statemnt not works in HANA works in oralce )

create row table "EFASHION_TUTORIAL"."AAA" as

select

"ARTICLE_COLOR_LOOKUP_ID",

"ARTICLE_ID",

"COLOR_CODE",

"ARTICLE_LABEL",

"COLOR_LABEL",

"CATEGORY",

"SALE_PRICE",

"FAMILY_NAME",

"FAMILY_CODE"

from "EFASHION_TUTORIAL"."ARTICLE_COLOR_LOOKUP";

2. Row & column table two different purpose like OLTP & OLAP.

when you think about OLAP means modeling use Column.

when you think about OLTP means real time operations then use Row

Column table is high compress ( 5 - 20X), i don't think you want get any performance issue when read information from column table. that is actual Core engine reading parller process. ( that is Heart of HANA).

Column table purpose quite different like calculations, grouping.. most of DW environment Queires.

Row table is currently system tables in feature row tables as OLTP, it's less compress mode compress to column store.

so finally you write small program convert column to row and row to column

thanks

Rao

[rajarshi_muhuri]

@Rao

you can alter row to column very simply

suppose X is a ROW table under schema TEST

ALTER TABLE TEST.X COLUMN

changes from type ROW to COLUMN

and to revert back, its as simple as

ALTER TABLE TEST.X ROW

[Former Member]

thank you i don't know this i will test it

[tomas-krojzl]

Hello,

Would this be a resource intensive operation if the table has a lot of data ?

If I remember correctly row based tables are fully loaded in memory during startup - so I guess that it not good idea to have row table with huge amount of data (I mean HUGE).

Lets suppose , if the above can be done , but there exists a interdependency for the column table (say from another simultaneous operation), and thus should remain as COLUMN table as such. so what would be the better option in this case.

In case you have two scenarios and you are not able to decide which is better. Then I would suggest to run benchmark using COLUMN, then another benchmark using ROW and then decide.

Tomas

[rajarshi_muhuri]

Tomas Wrote

If I remember correctly row based tables are fully loaded in memory during startup - so I guess that it not good idea to have row table with huge amount of data (I mean HUGE).

Are you saying that COLUMN tables dont load all data in memory during start up ?

and also you mention 'benchmark' . are there any standard built in benchmarking tools / commands to do the bench marking ?

Forgive me if my questions sound very rudimentary .. I am learning as I am going.. (inching slowly) , I have not read the standard HANA material end to end yet .

[tomas-krojzl]

Hello,

I would swear I was reading somewhere that ROW based tables are loaded during startup and COLUMN based tables are loaded on demand. (It would have logic assuming that we are in reporting space where you will have majority of column based tables and those few row based tables are expected to be small...)

However now I am not able to find this information anywhere - so let me do some research before I confirm... I do not wish to mislead you...

Tomas