Oracle-L: RE: Adding a new column

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 23 May 2003 11:27:11 -0800
Message-ID: <F001.005A1BA6.20030523112711@fatcity.com>

Brad

What problems are you anticipating? You can analyze the table and get a count of how many rows have migrated (CHAIN_CNT of USER_TABLES). Why does the presence of a varchar2(500) concern you, and how do you feel that rebuilding the table would make a difference?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, May 23, 2003 8: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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

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