DDL take a long time (ADD COLUMN)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DDL take a long time (ADD COLUMN)

  1. #1
    Join Date
    Nov 2005
    Location
    Brazil
    Posts
    22

    DDL take a long time (ADD COLUMN)

    Environment:

    Oracle Version: Oracle9i Enterprise Edition Release 9.2.0.7.0

    OS: Linux


    Hi all,

    I need to add a column in table (11 kk rows), see below:

    ALTER TABLE NOTAS_FISCAIS ADD (ID_QUEBRA_AGRUP VARCHAR2(50) DEFAULT 'X' NOT NULL);

    But this DDL take a long time, how can I do for this DDL to be faster? Is there any option?

    Thanks in advance.

    Leonardo.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1- Add column as allowing Null values, no default.
    2- at a time of low database activity run an update command populating all rows with the default value.
    3- Alter the column setting it as Not Null and with a default value.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you can use dbms_redefinition, it wont be faster but downtime is very low

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