Oracle-L: Adding a new column

From: Odland, Brad <Brad.Odland_at_qtiworld.com>
Date: Fri, 23 May 2003 05:56:50 -0800
Message-ID: <F001.005A150A.20030523055650@fatcity.com>

I am suspecting it is better to rebuild a table and add the new column in a CREATE TABLE AS SELECT.... type statement rather than a simple ALTER TABLE. THough in this casa rebuilding the table would have broken a load of code and been a more tricky process.

I noticed a developer here has added a column, a VARCHAR2(4) and then updated several hundred thousand rows to a production table that has 1.6 million rows 30 some odd columns with a VARCHAR2(500) comments field. The whole table is around 400 MB..

This table has 29 dependencies including views, procedures and triggers. Though because of the VARCHAR2(500) is probably not used fully (if at all) I think there should be plenty of room for this column addition without effecting the table. THoughts? I'm trying to determine if I should inform them that there may be problems later. (there are always problems later....what am I thinking...) I am least going to acknowledge that it was done.

Here are the specs on the table:

The table has a even mix of NUMBER, CHAR and VARCHAR2.

1.6 million rows - it is a time card like custom VB app table. - lots of stuff selecting out of this and people clocking time and face hugs the ERP system with loads of references to sales orders, work orders, mfg lines, prodcut lines etc...

PCTUSED 40
PCTFREE 20
INITRANS 2
MAXTRANS 255
STORAGE (

            INITIAL          173952K
            NEXT             43520K
            MINEXTENTS       1
            MAXEXTENTS       249
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Odland, Brad
INET: Brad.Odland_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri May 23 2003 - 08:56:50 CDT