best way to add a new column with value
I'm working on a DWH project using Oracle 18.104.22.168.
I have a compressed table (dimension) which has about 35M rows called CUST (customer). I got 15 tables, with an average of 100M rows, having a FK against the PK of the CUST table.
What I have to do is to add 2 new columns in my CUST table and they will never be null.
So my question is: which is the best way to add the 2 new columns.
Should I add the columns and then update the entire CUST table.
Should I create a new temp CUST table including the 2 new populated columns, disable the FK constraint realted to the CUST table, truncate the CUST table, insert into CUST the values from temp CUST and reenable the FK constraints?
Would be grateful for any advice
First issue you have is that you cannot currently add a column to a compressed table.
Which means that your best approach is CTAS ... Create Table As Select, with the new populated columns included, and the new table set to nologging and compress as part of the creation process.
Actually, this would be the bestapproach even without the add-columns problem, but that just reinforces it.
Further advice ... add extra spare columns when you create the new table,and leave pctfree sufficiently high to allow space for them to be populated later without getting row migration.
Click Here to Expand Forum to Full Width