Oracle-L: RE: Adding a new column

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 23 May 2003 10:42:00 -0800
Message-ID: <F001.005A1A6B.20030523104200@fatcity.com>

Brad,

why do you think you need to rebuild the table instead of adding the column? because of data updates?

I know of no good reason to rebuild a table when adding a column. It causes unnecessary downtime. One of the biggest benefits of a database system is it's flexibility to add columns - not like the old Cobol file system days.

I would add the column and walk away smiling.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, May 23, 2003 9:57 AM
To: Multiple recipients of list ORACLE-L

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).
--

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

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

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