CODEWITHSUNDEEP
databaseamazon-redshiftsnowflake-cloud-data-platform
iDataEngX
iDataEngX

Reputation: 339

Why can't columnar databases like Snowflake and Redshift change the column order?

I have been working with Redshift and now testing Snowflake. Both are columnar databases. Everything I have read about this type of databases says that they store the information by column rather than by row, which helps with the massive parallel processing (MPP).

But I have also seen that they are not able to change the order of a column or add a column in between existing columns (don't know about other columnar databases). The only way to add a new column is to append it at the end. If you want to change the order, you need to recreate the table with the new order, drop the old one, and change the name of the new one (this is called a deep copy). But this sometimes can't be possible because of dependencies or even memory utilization.

I'm more surprised about the fact that this could be done in row databases and not in columnar ones. Of course, there must be a reason why it's not a feature yet, but I clearly don't have enough information about it. I thought it was going to be just a matter of changing the ordinal of the tables in the information_schema but clearly is not that simple.

Does anyone know the reason of this?

Upvotes: 1

Views: 2364

Answers (3)

Joel Mamedov
Joel Mamedov

Reputation: 71

In some databases (Oracle especially) ordering columns on table will make difference in performance by storing NULLable columns at the end of the list. Has to do with how storage is beiing utilized within the data block.

Upvotes: 0

sprethepa
sprethepa

Reputation: 757

The order in which the columns are stored internally cannot be changed without dropping and recreating them.

Your SQL can retrieve the columns in any order you want.

General requirement to have columns listed in some particular order is for the viewing purpose.

You could define a view to be in the desired column order and use the view in the required operation.

CREATE OR REPLACE TABLE CO_TEST(B NUMBER,A NUMBER);
INSERT INTO CO_TEST VALUES (1,2),(3,4),(5,6);
SELECT * FROM CO_TEST;
SELECT A,B FROM CO_TEST;

CREATE OR REPLACE VIEW CO_VIEW AS SELECT A,B FROM CO_TEST;
SELECT * FROM CO_VIEW;

Creating a view to list the columns in the required order will not disturb the actual table underneath the view and the resources associated with recreation of the table is not wasted.

Upvotes: 0

Joe Harris
Joe Harris

Reputation: 14035

Generally, column ordering within the table is not considered to be a first class attribute. Columns can be retrieved in whatever order you require by listing the names in that order.

Emphasis on column order within a table suggests frequent use of SELECT *. I'd strongly recommend not using SELECT * in columnar databases without an explicit LIMIT clause to minimize the impact.

If column order must be changed you do that in Redshift by creating a new empty version of the table with the columns in the desired order and then using ALTER TABLE APPEND to move the data into the new table very quickly.

https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE_APPEND.html

Upvotes: 0

Related Questions