Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Unhappy Unanswered: Adding new column fields into a big table issue

    I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it??? I appreciate the help!


    J8

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're trying to add 150 million pieces of data (3 million rows times 50 columns) to your table, which now has 210 million pieces (3 million rows times 70 columns). That is significant growth (about 70%), so it will probably take quite a while.

    If the columns are all NULL-able, then I'd create a second "child" table that had the primary key from the first table and all of the new columns in it. That would allow you to populate them much more gracefully, possibly in stages.

    If you really need to add these columns to the existing table because of Referential Integrity issues or due to other reasons, then I'd strongly recommend declaring downtime (so you can force the users off of the system), then making the changes from Query Analyzer using the ALTER TABLE command. It still won't be fast, but it will be faster than any other method for doing this kind of job.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    33

    Smile

    Pat,

    For whatever reason, I have to stick those new columns into this big table. You can consider this table as kind of 'feed' table.

    Thanks for the tips.

    J8

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You should not have problems adding 50 columns providing they all allow NULL. If not then they must have a default, and you may bring your database down very easily (which what I suspect has happened) because every row must be updated with default value for that column or columns.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2004
    Posts
    52
    You can script the addition of columns and run it from query analyzer

    alter table mytable add col71 int null, col72 int null, ...

    as long as all of the columns are nullable this should happen instantly. I would expect EM to add them instantly too unless there is some difference between the default ANSI settings of the DB, Table, and your EM Session.

Posting Permissions

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