Skip to Content
Nov 27, 2011 at 08:15 AM

Alter table type from COLUMN to ROW


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