DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: best way to add a new column with value

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    best way to add a new column with value

    Hi,

    I'm working on a DWH project using Oracle 9.2.0.4.

    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.
    or
    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
    Mike

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width