Skip to Content
Technical Articles

Converting a row store table in HANA to a column store table

CONVERTING BETWEEN ROW STORE TO COLUMN STORE TABLE

Do you want to find out if there are any ROW store tables that were created accidentally in HANA and convert it into column store ? Use select on M_RS_TABLES with a where clause on SCHEMA_NAME.

Eg: select * from M_RS_TABLES where SCHEMA_NAME like ‘A%’; (Here our names users starts with an A, and hence I have specified as A% . You will have to modify as per your needs)

Note: Row store tables are always kept in-memory all the time and are loaded into HANA’s memory during start up and remain there irrespective of the fact whether they are used or not unlike column store .

It is not recommended to have row store tables on a user’s personnel schema in general and hence should be converted in general so that our start up times nor memory usage is not impacted because of this.

Steps:

1.To find out the list of row store table in HANA

select * from M_RS_TABLES;

You can also put and filter on the schema . Find the distinct schema that is present in ROW table(select distinct schema_name from M_RS_TABLES;) .Based on this we can also add where clause to above SQL.

2.To convert a row store table to HANA friendly column store tables .

ALTER TABLE <your_table_name> ALTER TYPE COLUMN

Post this conversion , you will not find this table in M_RS_TABLES . Instead you can find it in M_CS_TABLES .

NOTE:Default Table Type

The default table type has been changed in SAP HANA 2.0 SPS 03 to column-type tables. If the table type in an SQL CREATE TABLE statement is not explicitly stated then the table will automatically be created as a column table. The default table type for temporary tables remains unchanged as row-type.

 

Thanks for reading!
Follow for more such posts by clicking here and FOLLOW =>http://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!

Be the first to leave a comment
You must be Logged on to comment or reply to a post.