I have a table built incrementally with daily run. It cost now 17 TB to process. The size will increase over time. I regularly need to add new columns to this table and I'd like to be able to add these new columns not only at the end but in between after specific column.
So far the only way I found to do this is using the following statement:
create or replace table <SCHEMA.NEW_TABLE_NAME> as
select col1,col2,col3 from <SCHEMA.OLD_TABLE_NAME>;
However this is scanning the entire table and I don't want to have to run 17 TB processing each time I need to add a new column in between.
From what I've read I don't think it's possible but I don't think there any questions openly asking about that.
@SANN3 is absolutely right. When adding a new column to a table at the end, you can typically use the
ALTER TABLEstatement to add the column without needing to recreate the entire table. This is a common and efficient operation in most relational database management systems (RDBMS).This statement will add the new column to the end of the table's column list without the need to recreate the entire table. It's a straightforward and efficient way to extend the table's schema.
However, your initial question was about adding columns in between existing columns, which is a more complex operation. Many databases don't support adding columns in arbitrary positions without table recreation or significant data movement. In that case, the approach I previously described involving a temporary table and renaming can be used to achieve this.
But if you need to add columns at the end of the table, the ALTER TABLE statement is the way to go, and it won't require a full table scan or recreation.
Posting this answer as a Community Wiki since this is the only possible workaround and for the benefit of the community that might encounter this use case in the future.
Please feel free to edit this answer for additional information and if there are other possible workarounds/direct solutions for this use case.